SQL SELECT INTO – Make copy of SQL Table

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.

SYNTAX:

SELECT  *  INTO new_table [IN externaldb] FROM existing_table;

Example 1 – Make a copy of an existing table

You have the following table:

SqlSelectInto

With the following record count:

SqlSelectInto

If you want to make an exact copy, use the SQL SELECT INTO statement:

SqlSelectInto

Results in a new table “WeeklyPointsCopy” (notice the field names and data types are identical):

SqlSelectInto

With the same data content and record count (an exact copy):

SqlSelectInto

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:

SqlSelectInto

Results in a new table “WeeklyPointsQB” (notice the field names and data types are identical):

SqlSelectInto

This time, results in only data content where the “Position” = “QB”, which has 512 records:

SqlSelectInto

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:

SqlSelectInto

Results in a new table “WeeklyPointsSchema” (notice the field names and data types are identical):

SqlSelectInto

However, this table is empty (zero records):

SqlSelectInto

 

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.

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

Leave a Comment