Duplicate a table with or without the data

I have often been asked to purge data from a table, but if you work with databases, you know never to purge anything from the production database without having a restore plan, in case you need it back. I have used several tricks to ensure I don't lose data.

I've seen some folks export the data out to a spreadsheet, make a backup of the database etc. While it works, it is time consuming specially if you need to just preserve one table. A quick solution is to create another copy of the original table and then delete data from the main table. In order to quickly create an identical copy of the table along with data, you can run SELECT * INTO query.

For example, if I want to duplicate Orders table in Northwind database, I can run the following command...

SELECT * INTO OrdersCopy FROM Orders

What if I only want to create schema of a table and not copy the content. To achieve this, you can pass a false condition to WHERE clause in above script. For example...

SELECT * INTO OrdersCopy FROM Orders WHERE 1 = 0;

Any False condition will do and not just WHERE 1 = 0.

Basically SELECT * INTO statement first creates the schema and then evaluates the WHERE condition to copy data from the source to the destination table. You can also copy records that satisfy a certain criteria.

Thank you.