CODEWITHSUNDEEP
sql-servert-sql
LaysomeSmith
LaysomeSmith

Reputation: 681

SELECT INSERT INTO

Is it possible to insert data from select statement into a a dynamically created table? I will not know how many columns are there in select statement until I run the query. It has to create the appropriate number of columns at run time. Thank you, Smith

Upvotes: 1

Views: 351

Answers (3)

isakavis
isakavis

Reputation: 785

-- This creates temporary table

select * into #newtable from YourTable

select * from #newtable

-- This creates physical table in the DB

select * into newtable from YourTable

select * from newtable

Upvotes: 0

nyxthulhu
nyxthulhu

Reputation: 9752

You can use a CTAS pattern for this 

USE AdventureWorks
GO    
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Have a look at This Article on CTAS

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

Just use the SELECT INTO syntax:

SELECT *
INTO NewTable
FROM MyTable
WHERE ...

The new table NewTable will be created with the same columns as your select statement.

Upvotes: 4

Related Questions