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'
This question is related to
sql
sql-server
sql-server-2008
Try using SELECT INTO....
SELECT ....
INTO TABLE_NAME(table you want to create)
FROM source_table
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]
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)
Select [Column Name] into [New Table] from [Source Table]
Please try:
SELECT * INTO NewTable FROM OldTable
Source: Stackoverflow.com