SQL Server INSERT INTO SELECT By Practical Examples

Summary: in this tutorial, you will learn how to use the SQL Server INSERT INTO SELECT statement to add data from other tables to a table.

Introduction to SQL Server INSERT INTO SELECT statement

To insert data from other tables into a table, you use the following SQL Server INSERT INTO SELECT statement:

INSERT  [ TOP ( expression ) [ PERCENT ] ] 
INTO target_table (column_list)
query
Code language: SQL (Structured Query Language) (sql)

In this syntax, the statement inserts rows returned by the query into the target_table.

The query is any valid SELECT statement that retrieves data from other tables. It must return the values that are corresponding to the columns specified in the column_list.

The TOP clause part is optional. It allows you to specify the number of rows returned by the query to be inserted into the target table. If you use the PERCENT option, the statement will insert the percent of rows instead. Note that it is a best practice to always use the TOP clause with the ORDER BY clause.

SQL Server INSERT INTO SELECT examples

Let’s create a table named addresses for the demonstration:

CREATE TABLE sales.addresses (
    address_id INT IDENTITY PRIMARY KEY,
    street VARCHAR (255) NOT NULL,
    city VARCHAR (50),
    state VARCHAR (25),
    zip_code VARCHAR (5)
);   
Code language: SQL (Structured Query Language) (sql)

1) Insert all rows from another table example

The following statement inserts all addresses from the customers table into the addresses table:

INSERT INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name; 
Code language: SQL (Structured Query Language) (sql)

To verify the insert, you use the following query:

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

Here is the result:

2) Insert some rows from another table example

Sometimes, you just need to insert some rows from another table into a table. In this case, you limit the number of rows returned from the query by using conditions in the WHERE clause.

The following statement adds the addresses of the stores located in Santa Cruz and Baldwin to the addresses table:

INSERT INTO 
    sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.stores
WHERE
    city IN ('Santa Cruz', 'Baldwin')
Code language: SQL (Structured Query Language) (sql)

SQL Server returned the following message indicating that two rows have been inserted successfully.

(2 rows affected)
Code language: SQL (Structured Query Language) (sql)

3) Insert the top N of rows

First, you use the following statement to delete all rows from the addresses table:

TRUNCATE TABLE sales.addresses;
Code language: SQL (Structured Query Language) (sql)

Second, to insert the top 10 customers sorted by their first names and last names, you use the INSERT TOP INTO SELECT statement as follows:

INSERT TOP (10) 
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;
Code language: SQL (Structured Query Language) (sql)

SQL Server returned the following message showing that ten rows have been inserted successfully.

(10 rows affected)
Code language: SQL (Structured Query Language) (sql)

4) Insert the top percent of rows

Instead of using an absolute number of rows, you can insert a percent number of rows into a table.

First, truncate all rows from the addresses table:

TRUNCATE TABLE sales.addresses;
Code language: SQL (Structured Query Language) (sql)

Second, insert the top 10 percent of rows from the customers table sorted by first names and last names into the addresses table:

INSERT TOP (10) PERCENT  
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;
Code language: SQL (Structured Query Language) (sql)

SQL Server issued the following message indicating that 145 rows have been inserted successfully.

(145 rows affected)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server INSERT INTO SELECT statement to insert rows from other tables into a table.

Was this tutorial helpful?