Transact-SQL (TSQL) Quick Tips – SQL SELECT INTO Statement
The SQL SELECT INTO statement allows you to SELECT the data contents of one table and move it into a separate table. Essentially, you are making a copy of the original table (either full copy or partial, depending on your SELECT statement). SELECT INTO is based on a simple or complex SELECT statement, only you are taking the query results and using the INTO to create a brand new table. You can also use SQL SELECT INTO statement to copy only the Table Schema into a new table, which I will demonstrate below.
From MSDN: The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions like the following: The SELECT statement contains a join. Multiple SELECT statements are joined by using UNION. The identity column is listed more than one time in the select list. The identity column is part of an expression. The identity column is from a remote data source.
SELECT * INTO new_table [IN externaldb] FROM existing_table;
Example 1 – Make a copy of an existing table
You have the following table:
With the following record count:
If you want to make an exact copy, use the SQL SELECT INTO statement:
Results in a new table “WeeklyPointsCopy” (notice the field names and data types are identical):
With the same data content and record count (an exact copy):
Example 2 – Make a copy of an existing table, with subset of records
If you wanted to make a copy of the table, with a subset of the total records, you can modify the SELECT statement, like so:
Results in a new table “WeeklyPointsQB” (notice the field names and data types are identical):
This time, results in only data content where the “Position” = “QB”, which has 512 records:
Example 3 – Make a copy of an existing table (SCHEMA ONLY)
If you wanted to make a copy of a table, but only bring over the SCHEMA of the original table, simply add the following to the SQL SELECT INTO statement:
Results in a new table “WeeklyPointsSchema” (notice the field names and data types are identical):
However, this table is empty (zero records):
As you can see, the SQL SELECT INTO statement provides useful functionality when you are trying to copy one table into another. I find this statement very handy when I want to test out table changes or build a new table where I can base the data types and content on an existing.
If you would like to post any additional examples, please add them to the comments below.