How to create a table from select query result in SQL Server 2008
I want to create a table from select query result in SQL Server, I tried
create table temp AS select.....
but I got an error
Incorrect syntax near the keyword 'AS'
Use following syntax to create new table from old table in SQL server 2008
Select * into new_table from old_table
Read more... Read less...
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
SELECT col1, col2 INTO #a -- <<== creates temporary table FROM tablename
SELECT col1, ....., [email protected] -- <<== select as many columns as you want INTO [New tableName] FROM [Source Table Name]
Please be careful,
"SELECT * INTO NewTable FROM OldTable"
is not always the same as
"create table temp AS select.."
I think that there are occasions when this (in MSSQL) does not guarantee that all the fields in the new table are of the same type as the old.
For example :
create table oldTable (field1 varchar(10), field2 integer, field3 float) insert into oldTable (field1,field2,field3) values ('1', 1, 1) select top 1 * into newTable from oldTable
does not always yield:
create table newTable (field1 varchar(10), field2 integer, field3 float)
but may be:
create table newTable (field1 varchar(10), field2 integer, field3 integer)
Try using SELECT INTO....
SELECT .... INTO TABLE_NAME(table you want to create) FROM source_table
SELECT * INTO NewTable FROM OldTable
Select [Column Name] into [New Table] from [Source Table]