SQL Server SELECT INTO

The most efficient method of copying a result set into a new table is to use the SELECT INTO method. This method also follows a very simple syntax.

[cc lang=”sql”]
SELECT *
INTO dbo.NewTableName
FROM dbo.ExistingTable
[/cc]

Once the query above is executed, all the columns and data in the table ExistingTable (along with their datatypes) will be copied into a brand new table named: NewTableName. The reason this is so fast is because it makes use an internal SQL Server feature called minimal logging. Minimal logging means that each row that is written to the new table is only written to the data page and is not written to the log file. Instead only a pointer to the data is inserted to the log file. This cuts the IO down by half.

The query shown above is simple, but it can also be as complex as you’d like it to be. Any query that is currently returning a result set, can easily be made into a SELECT INTO query by inserting the INTO keyword after the select list.

The Downside of SELECT INTO

This title may be a little intense, however there are a couple issues that SELECT INTO does not cover. First, it does not copy over any of the constraints, keys, or computed columns. It also does not copy over the indexes. While this may not necessarily be a downside, it should be noted. Many times I have copied a table using SELECT INTO only to find I did not take into account that it does not copy the attributes.