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)