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'
-
Create table temp as Select 1 from dual --(from Oracle)Elmer– Elmer2013-05-22 08:17:47 +00:00Commented May 22, 2013 at 8:17
-
7Can the mark as duplicate be undone? The other question linked to is much less clear and has fewer answers.Ed Avis– Ed Avis2016-10-19 13:57:36 +00:00Commented Oct 19, 2016 at 13:57
-
Earlier duplicate: stackoverflow.com/q/8314435/946850krlmlr– krlmlr2019-04-11 18:18:21 +00:00Commented Apr 11, 2019 at 18:18
Add a comment
|
6 Answers
Use following syntax to create new table from old table in SQL server 2008
Select * into new_table from old_table
9 Comments
quant
This doesn't really answer the question. Where is the
SELECT query?
Lukas Eder
@Jason: It's right there. It's all around the
INTO clause...
Hack-R
@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 CTASLukas Eder
Mogli
Select * into your_new_table_name from (your select query here) as virtual_table_name table will be created with "your_new_table_name".
|
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, ....., col@ -- <<== select as many columns as you want
INTO [New tableName]
FROM [Source Table Name]
3 Comments
Led
this should be marked as correct answer.
Dickie Watkins
Agreed. This is the correct answer.
flow2k
I found the quoted doc here: learn.microsoft.com/en-us/sql/t-sql/queries/…. 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. dev.mysql.com/doc/refman/8.0/en/….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)
3 Comments
Hack-R
This is correct. There are a ton of repetitive solutions above that all miss this critical distinction.
HEDMON
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." (learn.microsoft.com/en-us/sql/t-sql/queries/…). Please, forgive my ignorance, am I missing something?
Pedro Lopes
@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.
Please try:
SELECT * INTO NewTable FROM OldTable
Comments
Try using SELECT INTO....
SELECT ....
INTO TABLE_NAME(table you want to create)
FROM source_table
Comments
Select [Column Name] into [New Table] from [Source Table]
