SQL Server SELECT INTO

Summary: in this tutorial, you will learn how to use the SQL Server SELECT INTO statement to copy a table.

Introduction to SQL Server SELECT INTO statement

The SELECT INTO statement creates a new table and inserts rows from the query into it.

The following SELECT INTO statement creates the destination table and copies rows, which satisfy the WHERE condition, from the source table to the destination table:

SELECT 
    select_list
INTO 
    destination
FROM 
    source
[WHERE condition]
Code language: SQL (Structured Query Language) (sql)

If you want to copy the partial data from the source table, you use the WHERE clause to specify which rows to copy. Similarly, you can specify which columns from the the source table to copy to the destination table by specifying them in the select list.

Note that SELECT INTO statement does not copy constraints such as primary key and indexes from the source table to the destination table.

SQL Server SELECT INTO examples

Let’s take some examples of using the SELECT INTO statement.

A) Using SQL Server SELECT INTO to copy table within the same database example

First, create a new schema for storing the new table.

CREATE SCHEMA marketing;
GO
Code language: SQL (Structured Query Language) (sql)

Second, create the marketing.customers table like the sales.customers table and copy all rows from the sales.customers table to the marketing.customers table:

SELECT 
    *
INTO 
    marketing.customers
FROM 
    sales.customers;
Code language: SQL (Structured Query Language) (sql)

Third, query data from the the marketing.customers table to verify the copy:

SELECT 
    *
FROM 
    marketing.customers;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

B) Using SQL Server SELECT INTO statement to copy table across databases

First, create a new database named TestDb for testing:

CREATE DATABASE TestDb;
GO
Code language: SQL (Structured Query Language) (sql)

Second, copy the sales.customers from the current database (BikeStores) to the TestDb.dbo.customers table. This time, we just copy the customer identification, first name, last name, and email of customers who locate in California:

SELECT    
    customer_id, 
    first_name, 
    last_name, 
    email
INTO 
    TestDb.dbo.customers
FROM    
    sales.customers
WHERE 
    state = 'CA';
Code language: SQL (Structured Query Language) (sql)

Third, query data from the TestDb.dbo.customers to verify the copy:

SELECT 
    * 
FROM 
    TestDb.dbo.customers;
Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

In this tutorial, you have learned how to use the SQL Server SELECT INTO statement to copy a table within the same database or across databases.