Problem
I’m trying to build a table in SQL Server using the results of a select query.
create table temp AS select.....
However, I encountered a problem.
Asked by yogesh9239
Solution #1
Use following syntax to create new table from old table in SQL server 2008
Select * into new_table from old_table
Answered by Sanjeev Rai
Solution #2
use SELECT…INTO
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]
Answered by John Woo
Solution #3
Please use caution, MSSQL: “SELECT * FROM OldTable INTO NewTable”
“create table temp AS select..” is not always the same as MYSQL: “create table temp AS select..”
I believe that in some cases, this (in MSSQL) does not ensure that all 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)
Answered by mssql-mysql
Solution #4
Please try:
SELECT * INTO NewTable FROM OldTable
Answered by TechDo
Solution #5
SELECT INTO is a good option….
SELECT ....
INTO TABLE_NAME(table you want to create)
FROM source_table
Answered by Rebika
Post is based on https://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008