Friday, April 23, 2010

Bookmark : Copy Table Structure Without Data Using 1 Line Query

Sometimes the simplest ideas are the best.
A brilliant script to quickly duplicate table structure on the fly!

This Copies the table structure , but not the data....

SELECT * FROM Person.Contact

SELECT * INTO Person.Contact_COPY FROM Person.Contact Where 1=2

SELECT * FROM Person.Contact_COPY

As does this version, which utilises TOP 0. Unusual, but it works....

SELECT * FROM Person.Contact

SELECT TOP 0 * INTO Person.Contact_COPY FROM Person.Contact

SELECT * FROM Person.Contact_COPY

This is very useful for creating temporary tables on the fly.

Things to note :
  • The table is created on the default filegroup.
  • Indexes/Constraints are not copied.
  • Any Identity column will be reseeded.

SSC :
Copy Table Structure Without Data Using 1 Line Query

No comments: