Coder Perfect

In SQL Server 2008, how to construct a table from a select query result [duplicate]


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



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….

INTO     TABLE_NAME(table you want to create)
FROM source_table

Answered by Rebika

Post is based on