T-SQL INSERT INTO SELECT and SELECT INTO

A common task when using Transact-SQL (T-SQL) is to copy information from one table into another, possibly changing the data or its structure in the same operation. This can be achieved by combining the standard SELECT and INSERT commands.

Copying Data Between Tables

It is common to want to copy information from one table into another using T-SQL, possibly within a stored procedure. This is useful when archiving older data from a table that is used heavily and should be kept small. It is also common to copy data into a temporary table prior to a long-running or complex operation, especially if that operation would otherwise compromise the performance of the system.

There are many ways in which information can be transferred between tables. In this article we will examine two that each require only a single line of T-SQL code.

INSERT INTO SELECT

The first method of copying data is to insert data using the INSERT command but instead of providing a VALUES clause containing the information for the new row, a SELECT statement is used as a subquery. The data generated from the select statement is added into the table defined in the INSERT. Any existing rows in the target table are unaffected.

If the schemas of the two tables are identical, you can perform the operation without specifying the columns that you wish to insert into, as in the following sample code:

INSERT INTO Table2
SELECT * FROM Table1

The above example will copy all of the information from Table1 into Table2. You can, of course, filter the rows with a WHERE clause, group the data or use other clauses to pre-process the information as long as the structure of the selected columns is compatible with the columns in the target table.

If you do not wish to insert data into every column of the target table you can provide a column list for both the INSERT and SELECT parts of the statement. Any columns in the target table that are omitted from the column list will have their default value or be NULL after the operation.

INSERT INTO Table2
(Column1, Column3)
SELECT Column1, Column3
FROM Table1

If you are copying information into a table with an IDENTITY column you should either omit that column from the list or enable inserting of identity values for the period of the operation.

SELECT INTO

The second method is to query the database to obtain and pre-process the information that you wish to copy and use the INTO clause to specify the name for a new table. The new table will be created using columns with the names and types defined in the SELECT statement. The names can differ from the original source columns if you apply new names using the AS clause.

This method can be faster and more efficient than the first if your database is configured to use the simple or bulk logged recovery method, as the logging generated by the statement will be minimal.

SELECT Column1, Column2, Column3
INTO NewTable
FROM Table1

This method of copying can also be used to create a new, empty table using the schema of another. To do so, simply add a WHERE clause that causes the query to return no data:

SELECT Column1, Column2, Column3
INTO NewTable
FROM Table1
WHERE 1 = 0

SQL TSQL T-SQL insert insert into select