Here’s an easy one, but sometimes still troublesome for individuals testing the waters of SQL: How to quickly copy a table. There are lots of cases where you need to duplicate a table in all but name. Maybe you want the data as well, maybe not. Unfortunately this seemingly simple task is lacking as a functionality in most development tools – including the big boys like MS SQL Server Management Studio.

The good news is there’s a perfectly logical reason such a feature isn’t apparent: It’s part of SQL already, and super simple to boot. Here’s what to do:

SELECT * INTO schema.new_table FROM schema.existing_table

Execute this as a query and voila! You now have a duplicate of the old table, complete with data. Want to make a copy sans data? Just query for a non existent key value.

SELECT * INTO schema.new_table FROM schema.existing_table WHERE some_field = non_existing_value

It’s really that simple. No plug ins or complex write ups required.

Extra Credit

• You needn’t copy every field. Swap out * with a list of desired fields, just like any other query.
• Play with the WHERE cause a bit and you can copy portions of data rather than all or nothing. Again, to your SQL engine it’s just another query, so use your imagination.