Advertisement
Advertisement


How to create a table from select query result in SQL Server 2008


Question

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'

2013/05/22
1
201
5/22/2013 5:05:04 AM

Accepted Answer

Use following syntax to create new table from old table in SQL server 2008

Select * into new_table  from  old_table 
2013/05/22
358
5/22/2013 5:14:23 AM

use SELECT...INTO

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.

Example,

SELECT col1, col2 INTO #a -- <<== creates temporary table
FROM   tablename

Standard Syntax,

SELECT  col1, ....., [email protected]      -- <<== select as many columns as you want
        INTO [New tableName]
FROM    [Source Table Name]
2013/05/22

Please be careful, MSSQL: "SELECT * INTO NewTable FROM OldTable"

is not always the same as MYSQL: "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)
2013/12/19

Try using SELECT INTO....

SELECT ....
INTO     TABLE_NAME(table you want to create)
FROM source_table
2013/05/22

Please try:

SELECT * INTO NewTable FROM OldTable
2013/05/22

Select [Column Name] into [New Table] from [Source Table]
2020/04/16