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.

SQL SERVER - Create Table From Another Table CreateTable-800x348

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)

, ,
Previous Post
SQL SERVER – Datatype Storing Unicode Character Strings
Next Post
SQL SERVER – 7 Questions about OUTPUT Clause Answered

Related Posts

3 Comments. Leave new

  • 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.

    Reply
  • David van Mersbergen
    October 12, 2020 6:04 pm

    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.

    Reply

Leave a Reply

Menu