TSQL Tip O’ the Week – 6/21/11 – Copy a Table Structure

This weeks tip:

Often I’ll need to copy a tables structure, i.e. all the columns and data types, to a new table.  Some examples of times when this is needed are when you are migrating data, staging data, etc.  A quick and easy way to do this is to select an empty record set into a new table.  For Example:

Select * into Table2 from Table1 where 1 = 2

Obviously one never equals two, so there will be no rows affected, but this will create Table2 with the same columns as Table1.

Note this method will include:

• All columns
• All data types and sizes
• Identity columns
• Nullability of all columns

Note this method will NOT include:

• Indexes
• Foreign keys
• Constraints
• Defaults
• Triggers

Here’s what it looks like in SSMS with the AdventureWorks database – I copied the Production.BillOfMaterials table to a new table called Production.BillOfMaterials2:

Here are some related posts from the intraweb:

• The INTO Clause page from Books Online for any other caveats – thanks for the suggestion Eric (blog/twitter)
• I found an article from Geeks with Blogs here: Copy a table into new table with/without data – SQL Server
• A good explanation from Caveman’s Blog here: Sql Server: How to copy a table?

Previous Tips O’ the Week

• TSQL Tip O’ the Week – 6/14/11 – The Empty GUID
• TSQL Tip O’ the Week – 6/6/11 – Declare and Assign Variables