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: