SQL SERVER – Create Table From Another Table

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)