Posts
33
Comments
186
Trackbacks
0
Dynamic table creation using SELECT INTO Clause in SQL

While working with SQL, you come across a scenario when you need to dump the resultset of a SELECT statement into either a temporary table or a placeholder table. I can imagine how tiresome it is to create a schema for the table beforehand and keep that updated with any additions/deletions to the SELECT statement.

To cut the long story short, SQL makes ones life easy with the help of a syntax "SELECT... INTO..."

E.g.

SELECT column1, column2,  column3

INTO NewDynamicTable

FROM TableName

This would create a new table 'NewDynamicTable'  with 3 coumns and populate it with the resultset of the SELECT statement.

Tip - If you want to create just schema for the table and does not want to populate it with the results of the select statement - use a where clause in the SELECT statement which won't yield any results.

SELECT column1, column2

INTO NewTable

FROM TableName

WHERE 1=2

Hope this helps!

posted on Tuesday, June 7, 2011 12:07 PM Print
Comments
Gravatar
# re: Dynamic table creation using SELECT INTO Clause in SQL
Sai Puli
6/7/2011 4:38 PM
There is a catch to that, it won't copy Constraints, Keys and Indexes etc. So if you also want the true schema copy, script the table or use different method, not SELECT... INTO. If you are looking for a temporary placeholder for some data, you can also consider CTE(Common Table Expression) some times it performs faster as it doesn't log the operations.
Gravatar
# re: Dynamic table creation using SELECT INTO Clause in SQL
Ho Hue
5/13/2014 11:03 AM
It's helpful! :) Tks alot :)
Gravatar
# re: Dynamic table creation using SELECT INTO Clause in SQL
Vipin
5/13/2014 1:14 PM
No worries ho, glad I could help

Post Comment

Title *
Name *
Email
Comment *  
Verification