Follow SQLQuill on Twitter


MN Pass

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:

Previous Tips O’ the Week

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

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>