MS SQL - Create a table from query results

By xngo on December 5, 2019

In MS SQL server, you can create a table from a query results using the SELECT INTO statement.

Copy exact table

SELECT * INTO new_table FROM old_table;

Copy with complex filtering conditions

It is not that hard to add more conditions to filter out the result set. You simply create your normal SELECT statement and then replace it with SELECT INTO statement. The sample statement below will create a new table from the left join results between Contacts and Emails tables.

SELECT * INTO new_table
    FROM Contacts AS c
        LEFT JOIN Emails AS e ON
   = e.Contact_id
    WHERE < 10


If you have duplicate column names due to multiple joins, MS SQL will terminate your query with the following error message:

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'id' in table 'new_table' is specified more than once.

In this case, you can't use SELECT * statement. You have to list all the column names and then ensure that each column name is unique by either rename it or discard it.

About the author

Xuan Ngo is the founder of He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.