INSERT INTO SELECT statement overview and examples

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 records as 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.

1 2	INSERT INTO table_name (Column1, Column 2....) VALUES (value1, value2, ...);

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.

1 2	INSERT INTO table_name VALUES (value1, value2, ...);

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

CREATE TABLE Employees

(ID INT,

Name VARCHAR(20)

);

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

1 2	Insert into Employees (ID, Name) values (1,'raj') Insert into Employees values (2,'raj')

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.

INSERT INTO table1 (col1, col2, col3, …)

SELECT col1, col2, col3, …

FROM table2

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.

Let us create another table Customers with the following query.

CREATE TABLE Customers

(ID INT,

Name VARCHAR(20)

);

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.

INSERT INTO Customers

SELECT *

FROM Employees;

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

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

CREATE TABLE Customers

(ID INT IDENTITY(1, 1),

Emp_ID INT,

Name VARCHAR(20),

City VARCHAR(20) NULL,

);

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 Customers

SELECT *

FROM Employees;

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

INSERT INTO Customers (Emp_ID ,Name)

SELECT *

FROM Employees;

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.

Suppose you have a different column in the source table. You can still insert records into the 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.

1 2	ALTER TABLE Employees ADD Country varchar(50);

Update the table records with country value India.

1	Update Employees set Country='India'

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

INSERT INTO Customers (Emp_ID ,Name)

SELECT *

FROM Employees;

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

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

INSERT INTO Customers

(Emp_ID,

Name

)

SELECT ID,Name

FROM Employees;

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

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

INSERT TOP(1) INTO Customers

(Emp_ID,

Name

)

SELECT ID,Name

FROM Employees;

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 it 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 the City column.

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

1 2	INSERT TOP(1) INTO Customers (Emp_ID, Name, City) SELECT ID, Name,'Delhi' FROM Employees;

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

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 the 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.

CREATE TABLE [HumanResources].[EmployeeData](

[FirstName] [dbo].[Name] NOT NULL,

[MiddleName] [dbo].[Name] NULL,

[LastName] [dbo].[Name] NOT NULL,

[Suffix] [nvarchar](10) NULL,

[JobTitle] [nvarchar](50) NOT NULL,

[PhoneNumber] [dbo].[Phone] NULL,

[PhoneNumberType] [dbo].[Name] NULL,

[EmailAddress] [nvarchar](50) NULL,

[City] [nvarchar](30) NOT NULL,

[StateProvinceName] [dbo].[Name] NOT NULL,

[PostalCode] [nvarchar](15) NOT NULL,

[CountryRegionName] [dbo].[Name] NOT NULL

) ON [PRIMARY]

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.

INSERT INTO HumanResources.EmployeeData

SELECT p.[FirstName],

p.[MiddleName],

p.[LastName],

p.[Suffix],

e.[JobTitle],

pp.[PhoneNumber],

pnt.[Name] AS [PhoneNumberType],

ea.[EmailAddress],

a.[City],

sp.[Name] AS [StateProvinceName],

a.[PostalCode],

cr.[Name] AS [CountryRegionName]

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]

INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]

INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]

LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]

LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID];

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.

We define CTE by a WITH clause before SELECT, INSERT, UPDATE, DELETE statement
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.

WITH EmployeeData_Temp([FirstName],

[MiddleName],

[LastName],

[Suffix],

[JobTitle],

[PhoneNumber],

[PhoneNumberType],

[EmailAddress],

[City],

[StateProvinceName],

[PostalCode],

[CountryRegionName])

AS (

SELECT p.[FirstName],

p.[MiddleName],

p.[LastName],

p.[Suffix],

e.[JobTitle],

pp.[PhoneNumber],

pnt.[Name] AS [PhoneNumberType],

ea.[EmailAddress],

a.[City],

sp.[Name] AS [StateProvinceName],

a.[PostalCode],

cr.[Name] AS [CountryRegionName]

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]

INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]

INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]

INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]

LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]

LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID])

INSERT INTO HumanResources.EmployeeData

SELECT *

FROM EmployeeData_Temp;

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.

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

DECLARE @TableVar table(

[JobTitle] [nvarchar](50) NOT NULL,

[BirthDate] [date] NOT NULL,

[MaritalStatus] [nchar](1) NOT NULL,

[Gender] [nchar](1) NOT NULL,

[HireDate] [date] NOT NULL,

[SalariedFlag] [dbo].[Flag] NOT NULL,

[VacationHours] [smallint] NOT NULL,

[SickLeaveHours] [smallint] NOT NULL

)

-- Insert values into the table variable.

INSERT INTO @TableVar

SELECT

[JobTitle]

,[BirthDate]

,[MaritalStatus]

,[Gender]

,[HireDate]

,[SalariedFlag]

,[VacationHours]

,[SickLeaveHours]

FROM [AdventureWorks2017].[HumanResources].[Employee]

-- View the table variable result set.

SELECT * FROM @TableVar;

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023

The INSERT INTO SELECT statement

The syntax of the INSERT INTO

INSERT INTO SELECT Statement Syntax

INSERT INTO SELECT examples

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

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

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

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

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

Example 6: INSERT INTO SELECT statement with common table expression

Example 7: INSERT INTO SELECT statement with a Table variable

Conclusion

Related posts: