Here’s an easy one, though it can still be troublesome for people just getting started with SQL: how to quickly copy a table.

There are plenty of situations where you may need to duplicate a table in everything but name. Maybe you want to copy the data as well, maybe you only need the structure. Unfortunately, this seemingly simple task is not always obvious in development tools – even in major tools like Microsoft SQL Server Management Studio.

Fortunately, there is a perfectly logical reason the feature may not stand out: it is already part of SQL, and it is very simple.

To copy a table with its data, run: 

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.

One last note: SELECT INTO copies the selected columns and data, but it does not automatically copy indexes, keys, constraints, triggers, or permissions. For a quick working copy, it is great. For a true production-ready duplicate, remember to review those additional table objects.