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.

SELECT *
INTO dbo.NewTableName
FROM dbo.ExistingTable

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.

2 comments
Skiilz 25 Jul 2011 at 10:29 am

How do you enforce a strict output structure when using a ‘select into’?

I am taking the union of two or more VERY large tables so using a select into is great.. But I need the output structure to be defined.

I know I can ‘cast’ and ‘convert’ the data to the desired structure but this truncates the data.
I need to KNOW when my data is being truncated (as I would when using an ‘INSERT’ statement).

i.e. I want the program to blow up rather than ignore the data mis-match.

~Skiilz

Featured Articles

 Site Author

  • Thanks for visiting!