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.
|
1 2 3 4 |
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.
|
1 2 3 |
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.
|
1 2 3 4 |
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.
|
1 2 3 |
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
|
1 2 3 4 5 6 |
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.
|
1 2 3 |
INSERT INTO Customers SELECT * FROM Employees; |
In this case, we need to specify the column name with INSERT INTO statement.
|
1 2 3 |
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.
|
1 2 3 |
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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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] GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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]; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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; GO |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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; GO |
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.
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
- 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