Rajendra Gupta
SQL INSERT INOT SELECT Output of query

INSERT INTO SELECT statement overview and examples

April 12, 2019 by

This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples and use cases.

In my earlier article SQL SELECT INTO Statement, we explored the following tasks.

  • Create a SQL table on the fly while inserting records with appropriate data types
  • Use SQL SELECT INTO to insert records in a particular FileGroup
  • We cannot use it to insert data in an existing table

The INSERT INTO SELECT statement

We want to insert record as a regular database activity. We can insert data directly using client tools such as SSMS, Azure Data Studio or directly from an application. In SQL, we use the SQL INSERT INTO statement to insert records.

The syntax of the INSERT INTO

Once we insert data into the table, we can use the following syntax for our SQL INSERT INTO statement.

If we have specified all column values as per table column orders, we do not need to specify column names. We can directly insert records into the table.

Let us create a sample table and insert data into it.

We can insert data using the following queries. Both queries are valid for data insertion.

We cannot insert data without specifying column names if there is a mismatch between data insertion and the order of column values is different. We can get the following error message.

  • Msg 213, Level 16, State 1, Line 6

    Column name or number of supplied values does not match table definition.

  • Msg 245, Level 16, State 1, Line 6

    Conversion failed when converting the varchar value ‘raj’ to data type int.

In this example, we’ll use the SQL INSERT INTO statement with supplying values directly in a statement. Suppose we want to insert data from another table. We can still use the SQL INSERT INTO statement with a select statement. Let’s explore this in the next section.

INSERT INTO SELECT Statement Syntax

We can insert data from other SQL tables into a table with the following INSERT INTO SELECT statement.

This query performs the following tasks:

  • It first Selects records from a table ( Select statement)
  • Next, it inserts into a table specified with INSERT INTO

  • Note: The Column structure should match between the column returned by SELECT statement and destination table.

INSERT INTO SELECT examples

Example 1: insert data from all columns of source table to destination table

We have the following records in an existing Employee table.

INSERT INTO SELECT examples

Let us create another table Customers with the following query.

We want to insert all records from the Employees table to the Customers table. We can use the SQL INSERT INTO SELECT statement to do this.

It inserts all records into the Customers table. We can verify the records in Customers table are similar to the Employees table.

sample data

In this example, we inserted records for all columns to the Customers table.

Example 2: Insert rows from source to destination table by specifying column names

Let’s drop the existing Customers table before we move forward. Now, we want to create a table with one additional IDENTITY column. IDENTITY column automatically inserts identity values in a table. We also added a City column that allows NULL values

We cannot use the INSERT INTO SELECT statement similar to the above example. If we try to run this code, we get an error message.


INSERT INTO SELECT examples and errors

In this case, we need to specify the column name with INSERT INTO statement.

In the Customers table, we have an additional column with allows NULL values. Let’s run a Select on Customers table. In the following screenshot, we can see NULL values in the City column.

INSERT INTO SELECT examples sample data

Suppose you have a different column in the source table. You can still insert records into destination table with specifying column names in the INSERT INTO SELECT statement. We should have an appropriate data type to insert data. You cannot insert a varchar column data into an INT column.

Add a new column in Employees table using ALTER TABLE statement.

Update the table records with country value India.

Now, rerun the INSERT INTO SELECT statement. You can notice that we are using SELECT * instead of specifying column names.

We get the following error message. This error comes because of column mismatch between the source table and destination table.

SQL Server INSERT INTO SELECT Error messsage

We can map the column between the source and destination table using the following query.

Example 3: Insert top rows using the INSERT INTO SELECT statement

Suppose we want to insert Top N rows from the source table to destination table. We can use Top clause in the INSERT INTO SELECT statement. In the following query, it inserts top 1 row from Employees table to Customers table.

Example 4: Insert using both columns and defined values in the SQL INSERT INTO SELECT Statement

In previous examples, we either specified specific values in the INSERT INTO statement or used INSERT INTO SELECT to get records from the source table and insert into the destination table.

We can combine both columns and defined values in the SQL INSERT INTO SELECT statement.

We have the following columns in the Customers and Employees table. Previously, we did not insert any values for the City column. We do not have the required values in the Employee table as well. We need to specify an explicit value for City column.

Tables columns comparison

In the following query, we specified a value for City column while the rest of the values we inserted from the Employees table.

In the following query, we can see it inserts one row (due to Top (1) clause) along with value for City column.

sample data

Example 5: INSERT INTO SELECT statement with Join clause to get data from multiple tables

We can use a JOIN clause to get data from multiple tables. These tables are joined with conditions specified with ON clause. Suppose we want to get data from multiple tables and insert into a table.

In this example, I am using AdventureWorks2017 database. First, create a new table with appropriate data types.

This table should contain records from the output of a multiple table join query. Execute the following query to insert data into HumanResources.EmployeeData table.


SQL INSERT INTO SELECT sample data

Example 6: INSERT INTO SELECT statement with common table expression

We use Common Table Expressions (CTE) to simplify complex join from multiple columns. In the previous example, we used JOINS in a Select statement for inserting data into a SQL table. In this part, we will rewrite the query with CTE.

In a CTE, we can divide code into two parts.

  1. We define CTE by a WITH clause before SELECT, INSERT, UPDATE, DELETE statement
  2. Once we define CTE, we can take reference the CTE similar to a relational SQL table

Execute the following code to insert data using a CTE.

Example 7: INSERT INTO SELECT statement with a Table variable

We use Table variables similarly to a temporary table. We can declare them using the table data type. This table can be used to perform activities in SQL Server where we do not require a permanent table. You can divide the following query into three parts.

  1. Create a SQL Table variable with appropriate column data types. We need to use data type TABLE for table variable
  2. Execute a INSERT INTO SELECT statement to insert data into a table variable
  3. View the table variable result set

SQL INSERT INTO SELECT Output of query

Conclusion

In this article, we explore the use cases of the INSERT INTO SELECT statement. I hope you found this article helpful. Feel free to provide feedback in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

17,759 Views