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'

Can the mark as duplicate be undone? The other question linked to is much less clear and has fewer answers. — Ed Avis, Oct 19 '16 at 13:57
Earlier duplicate: https://stackoverflow.com/q/8314435/946850 — krlmlr, Apr 11 '19 at 18:18

score 452 · Accepted Answer · answered May 22 '13 at 05:14

452

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

Select * into new_table  from  old_table

answered May 22 '13 at 05:14

Sanjeev Rai

6,721
4
23
33

17

This doesn't really answer the question. Where is the `SELECT` query? – quant Jan 15 '14 at 06:05
8

@Jason: It's right there. It's all around the `INTO` clause... – Lukas Eder Jul 22 '14 at 10:04
4

@LukasEder No, he means the `select` that would be there in `CREATE TABLE...AS SELECT...` which is currently where Sanjeev has `old_table`. This is a nice line of code but it lacks the functionality of CTAS because it's only selecting from a table, not a `SELECT` statement, which is the point of doing CTAS – Hack-R Oct 16 '14 at 20:31
54

@Hack-R: [`SELECT * INTO new_table FROM (SELECT ...) as [fancy derived table]`](http://sqlfiddle.com/#!3/d41d8/40242) – Lukas Eder Oct 17 '14 at 07:48
2

Select * into your_new_table_name from (your select query here) as virtual_table_name table will be created with "your_new_table_name". – Mogli Aug 10 '16 at 08:13
SELECT INTO is seriously deficient as is declare TABLE when you want to create duplicate table on the fly, in order to copy out records to temp table modify then then add them back as appended records. You there is already an object named 'x' in the database if 'x' was your temp database name. And the caveat being you don't want to specify all the column names again. It's meta data, should be easy to do. This is where create table as shines – JGFMK Jul 06 '17 at 17:17
This solution still worked for me in 2019 – Evan Gertis May 30 '19 at 13:45
Great, thank you @Hack-R . What does the `as [fancy derived table]` do? It doesn't seem to matter what I put inside it – FranLegon Dec 27 '22 at 12:00
@FranLegon https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-ctas – Hack-R Dec 27 '22 at 18:32

score 117 · Answer 2 · answered May 22 '13 at 05:02

117

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

Inserting Rows by Using SELECT INTO

Standard Syntax,

SELECT  col1, ....., col@      -- <<== select as many columns as you want
        INTO [New tableName]
FROM    [Source Table Name]

answered May 22 '13 at 05:02

John Woo

258,903
69
498
492

7

this should be marked as correct answer. – Led Oct 20 '17 at 07:30
2

Agreed. This is the correct answer. – Dickie Watkins Dec 20 '17 at 11:59
I found the quoted doc here: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver15. While this works great for SQL Server, it appears `SELECT ... INTO` is not standard SQL (and thus not portable to other DBMS's) - sometimes similar syntax does not create a new table, but instead appends the data into an existing table, e.g. https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html. – flow2k May 19 '20 at 08:07

score 35 · Answer 3 · edited Dec 19 '13 at 10:18

35

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)

edited Dec 19 '13 at 10:18

Amicable

3,115
3
49
77

answered Dec 19 '13 at 09:57

mssql-mysql

359
3
2

2

This is correct. There are a ton of repetitive solutions above that all miss this critical distinction. – Hack-R Oct 16 '14 at 20:32
11

from official docs for **'SELECT - INTO Clause (Transact-SQL)'** : "Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list." (https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql). Please, forgive my ignorance, am I missing something? – HEDMON Apr 11 '17 at 07:26
1

@HEDMON I don't think this was maybe the case when this answer was posted. I just tested the code above got the answer as in the official docs. field3 was created with float datatype like on the old table. – Pedro Lopes Jul 16 '18 at 13:02

score 19 · Answer 4 · answered May 22 '13 at 05:03

19

Please try:

SELECT * INTO NewTable FROM OldTable

answered May 22 '13 at 05:03

TechDo

18,398
3
51
64

score 15 · Answer 5 · answered May 22 '13 at 05:06

15

Try using SELECT INTO....

SELECT ....
INTO     TABLE_NAME(table you want to create)
FROM source_table

answered May 22 '13 at 05:06

Rebika

179
5

score 6 · Answer 6 · edited Apr 16 '20 at 11:18

6

Select [Column Name] into [New Table] from [Source Table]

edited Apr 16 '20 at 11:18

Rumi

3
2

answered May 22 '13 at 05:03

Prabhash Jha

330
2
12

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

6 Answers6

Linked

Related