Often during Comprehensive Database Performance Health Check, we have some free time when we can discuss interesting technical stuff. One of the DBAs from the client-side recently asked me if I know any easier way to create a table from another table without generating a CREATE TABLE script. Yes, it is totally possible to create a table from the existing table with a few simple tricks.
Trick 1: Using WHERE 1 = 2
This has to be one of the most popular tricks out there. I have used that in the past and still use it quite often. Here is the script which creates a new table based on the existing table.
SELECT * INTO NewTable FROM ExistingTable WHERE 1 = 2
Here if you do not want all the columns, you can specify the name of the column as well, instead of SELECT *.
SELECT col1, col2, col3 INTO NewTable FROM ExistingTable WHERE 1 = 2
The condition WHERE 1 = 2 will return no rows and the new table will be created with no rows.
Trick 2: Using TOP 0 for Create Table
This trick is not as popular as the trick 1 but still many use it efficiently.
SELECT TOP 0 * INTO NewTable FROM ExistingTable
This time no rows will be selected in the NewTable as we are using the TOP operator with the 0 as a parameter.
Just like the previous case, you can select columns instead of TOP 0.
SELECT TOP 0 col1, col2, col3 INTO NewTable FROM ExistingTable
Well, that’s it. I hope you find this method useful. I am not familiar with any third trick so far except CREATE TABLE. Let me know if you have any idea and I will be happy to post the same on the blog with due credit to you. You may reach out to me if you have any questions on LinkedIn.
Reference: Pinal Dave (https://blog.sqlauthority.com)
It is probably worth mentioning that the SELECT * INTO method will not create any indexes or constraints in the new table, that are present in your source table.
Of course they do not create any other schema object. You are correct.
I have used this method of creating a new table. I listed the columns and data type, then used a 3rd party source control or deployment application to reinstate the primary/foreign keys. indexes and constraints.
Why do you need 1 = 2? Isn’t it the same if you don’t specify where criteria for first and second examples?
It is required to create a table schema and not data.
how to inforce primary keys and foreign keys through this method.