create table from output of select sql

I want to create a table with the output of select statement. i.e. create
table tmpk as select *...
this gives an error
Incorrect syntax near the keyword 'as'.
when i am running query alone this gives the result fine.try
SELECT
field1, field 2 etc...
INTO tmpk
FROM
wherever
"Karn Tanwar" <karntanwer@.hotmail.com> wrote in message
news:OtJceVhVFHA.544@.TK2MSFTNGP15.phx.gbl...
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>|||On Wed, 11 May 2005 16:21:36 +0530, Karn Tanwar wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
You have to use EXEC, like this:
DECLARE @.var VARCHAR(8000)
SELECT @.var = 'CREATE TABLE ' + something
FROM somewhere
WHERE somecol = @.somevar
EXEC (@.var)
This is a crude example, but you get the point.
/Andrs Taylor|||Hi Karan
Try this way
SELECT * INTO tmpk FROM ...
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Karn Tanwar" wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Unfortunately in the SQL Server enviornment you cannot select into a table
like you can in other legacy enviornments like Foxpro but you can declare a
local temp table with the same field makeup as the select statement outputs
and then use and insert to get the data into a table. You might have to mes
s
with it for a little while before you get it right, but it is possible to ge
t
somewhat the same results.
Declare @.v_sql NVARCHAR(4000)
SELECT @.v_sql =
INSERT #test
select hard_id,
Type_id,
description,
speed,
amount,
cache,
brand
from TU_Hardware
EXEC sp_executesql @.v_sql
"Karn Tanwar" wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Correction.. you can do a select into statement but you cant do it the way
you were trying it.
If you go to masters table on any db and type the following:
select * from abc
It will give you an error because no table exist then type
select top 10 *
into abc
from sysobjects
You will have a new table in master db. You can do this with any table in
the from part of the statement.
Hope this helps..
"Karn Tanwar" wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>