How to create a table from select query result in

Question

This question already has an answer here:

• How to create table using select query in SQL Server? 3 answers

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'

Answer 1

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]

Answer 2

Try using SELECT INTO....

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

Answer 3

Select [Field Names] into [New Table] from [Source Table]

Answer 4

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

Select * into new_table  from  old_table 

Answer 5

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)

Answer 6

Please try:

SELECT * INTO NewTable FROM OldTable