This article explains the different approaches used to insert multiple rows into SQL Server tables.
Inserting rows using the INSERT INTO VALUES command
The most basic approach to insert multiple rows in SQL is using the INSERT INTO VALUES command. Several SQL developers think that this command is meant to insert only a single row.
The INSERT INTO VALUES command is used to insert data into all columns or specific columns of a table. If data is inserted into specific columns, they should be specified after the table name. Moreover, the values inserted should be specified within two parentheses after the VALUES keyword. The inserted values might be provided manually or using variables. For example:
|
1 2 3 |
INSERT INTO [dbo].[Customers] ([first_name], [middle_name], [last_name], [birth_date], [nationality]) VALUES('William', 'Henry', 'Gates', '19551028', 'US') |
In the SQL command above, “[dbo].[Customers]” is the table name, “[first_name], [middle_name], [last_name], [birth_date], [nationality]” are the columns name, and “‘William’, ‘Henry’, ‘Gates’, ‘19551028’,1” are the values we are looking to insert.
In order to insert data into all columns, there is no need to specify them explicitly, but we should make sure that values are provided in the correct order.
|
1 2 3 |
INSERT INTO [dbo].[Customers] VALUES('William', 'Henry', 'Gates', '19551028', 'US') |
To insert multiple rows in SQL, we should specify several groups of values separated by commas; each group should be enclosed within two parentheses, as shown below:
|
1 2 3 4 |
INSERT INTO [dbo].[Customers] ([first_name], [middle_name], [last_name], [birth_date], [nationality]) VALUES('William', 'Henry', 'Gates', '19551028', 'US'), ('Mark', 'Glenn', 'Callaway', '19501020', 'US'), ('Mehmet', 'Fateh', 'Shoul', '19601020', 'TK') |
The following screenshot shows how data is inserted into the customers’ table:
Figure 1 – Inserting multiple rows using the INSERT INTO VALUES command
Inserting rows using the INSERT INTO SELECT command
The INSERT INTO SELECT command is the second approach to insert multiple rows in SQL. This approach is commonly used when inserting data from one table into another. Still, other use cases will be illustrated in this section.
Inserting data from one table or view into another
To insert data from one table into another, we should specify the destination table and columns similar to the INSERT INTO VALUES commands. Then, we should specify the selected query that reads the data from the source table or view.
|
1 2 3 4 5 6 7 8 |
INSERT INTO [dbo].[Customers] ([first_name],[middle_name],[last_name],[birth_date],[nationality]) SELECT DISTINCT [first_name],[middle_name],[last_name],[birth_date],[nationality] FROM [dbo].[Customers_] WHERE [nationality] IS NULL |
The below screenshot illustrates how multiple rows were inserted from the Customers_ table into the Customers table.
Figure 2 – Inserting multiple rows using INSERT INTO SELECT command
In case both tables have the exact columns count, types, and order. We can use a SELECT * query to insert multiple rows in SQL as follows:
|
1 2 3 4 5 6 |
INSERT INTO [dbo].[Customers] SELECT * FROM [dbo].[Customers_] WHERE [nationality] IS NULL |
Inserting data from multiple tables/views into one table
The SELECT command doesn’t need to read from a single table or view. We can use a more complex query with INNER and OUTER joins. We should always ensure that the destination columns match the columns returned by the SQL Command, as shown in the screenshot below:
Figure 3 – Inserting multiple rows from several tables
Inserting data into a view
Inserting data into a view means that the data is inserted into the underlying table. To illustrate this, we created a view vCustomers using a SELECT * query from the Customers table. Then we visualized the estimated execution query plan for two SQL queries; the first inserts the data into the vCustomers view and the second into the Customers table. As shown in the screenshot below, both execution plans are identical.
Figure 4 – Queries execution plans
Inserting data into view is valid only when we need to allow inserting data into specific columns without allowing users to insert into all columns in the destination table.
Inserting data from a subquery into a table
Subqueries are another option to insert multiple rows in SQL. They can be used as a data source in an INSERT INTO SELECT command. Subqueries are useful when we need more than one query to obtain the result we want, and each subquery returns a subset of the table involved with the query. For example:
|
1 2 3 4 5 |
INSERT INTO [dbo].[Customers] SELECT * FROM (SELECT c.[first_name],c.[middle_name],c.[last_name],c.[birth_date],ct.[CountryAlpha2] FROM [dbo].[Customers_] c INNER JOIN [dbo].[countries] ct on c.nationality = ct.[Id]) AS Table_1 |
If you are interested in subqueries, you can refer to my previously published article on SQL Shack: How to write subqueries in SQL.
Inserting data from a common table expression into a table
Common table expression (CTE) can also be used as a data source. As many developers think they are similar, CTEs differ from subqueries as they are recursive, reusable, and could be more readable.
Figure 5 – Inserting data from a recursive common table expression
Inserting data from a table of values into a table
Another option to insert multiple rows in SQL is using a SELECT command against a table of values provided using the VALUES keyword. The column names must be explicitly provided, and the number of columns for each row in a table value constructor must be the same. For example:
|
1 2 3 4 5 6 7 |
INSERT INTO dbo.countries(id, country_name) SELECT DISTINCT * FROM ( VALUES (1, 'US'), (1, 'US'), (1, 'US'), (2, 'LB'), (5, 'UK'), (1, 'US'), (6, 'IT') ) AS X(id, country) |
In the SQL command above, four rows should be inserted in the countries table as shown in the screenshot below.
Figure 6 – Inserting rows using a table of values
Inserting data from a stored procedure result set into a table
In case a stored procedure output a result set using a SELECT command, this result set can be inserted into a table using an INSERT INTO EXEC command. For example, let us consider the following stored procedure that returns employees’ sales YTD.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD AS SET NOCOUNT ON; SELECT LastName, SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID; RETURN; GO |
To insert the data retrieved by this stored procedure into another table, we should use the following SQL command:
|
1 2 3 4 |
INSERT INTO dbo.EmployeeSalesYTD EXEC Sales.uspGetEmployeeSalesYTD |
Moreover, we can modify the result set of a stored procedure as per a definition specified in a WITH RESULT SETS clause as shown below:
|
1 2 3 4 5 6 7 8 |
INSERT INTO dbo.EmployeeSalesYTD EXEC Sales.uspGetEmployeeSalesYTD WITH RESULT SETS (( LastName varchar(50), SalesYTD money )) |
For more information about the EXEC command, you can refer to the following article: EXEC SQL overview and examples.
Inserting data from a table-valued function into a table
A table-valued function may also be used to insert multiple rows in SQL using an INSERT INTO SELECT statement, as shown in the following example:
|
1 2 3 4 5 |
INSERT INTO dbo.Employees( PersonID, FirstName, LastName, JobTitle, BusinessEntityType) SELECT PersonID, FIrstName, LastName, JobTitle, BusinessEntityType FROM [dbo].[ufnGetContactInformation](@PersonID) |
To learn more about table-valued functions, you can refer to the following articles:
Inserting data using the OPENQUERY
To insert data from a remote database, we can use the OPENQUERY statement. For example, the following SQL command reads the result set of an MDX query executed over a linked server mapping to an SSAS instance and then stores the result into a temp table.
|
1 2 3 4 5 |
INSERT INTO #TBLTEMP SELECT * FROM OPENQUERY(SSAS_LINKEDSERVER,'SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works DW2014]') |
To learn more about OPENQUERY and how to link an OLAP cube to a relational database, you can refer to the following article: Linking relational databases with OLAP cubes.
Inserting data using the OPENROWSET, OPENDATASOURCE
It is also possible in SQL to insert multiple rows from a remote database or an external data source such as Microsoft Excel files, text files, comma-separated, and other sources using the OPENROWSET and OPENDATASOURCE statements. For example, the following example reads the data from a Microsoft Access database and inserts it into a SQL table using the OPENROWSET statement:
|
1 2 3 4 5 6 |
INSERT INTO dbo.Customers SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\SAMPLES\Northwind.mdb';'admin';'password', Customers) AS a; Customers) AS a; |
More information about the OPENROWSET and OPENDATASOURCE commands can be found in the official documentation:
Inserting rows using the SELECT INTO command
A common approach in SQL to insert multiple rows into a new table is the SELECT INTO FROM command. This method is considered faster than the INSERT INTO method since it is minimally logged, assuming proper trace flags are set. In general, this method is commonly used during the data stagging phase or when creating temp tables.
We should specify the name of the table we are looking to create after the INTO keyword, while the table metadata will be defined based on the SELECT query output.
|
1 2 3 4 5 |
SELECT TOP (1000) [BusinessEntityID],[PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailPromotion], [AdditionalContactInfo],[Demographics],[rowguid],[ModifiedDate] INTO #Temp_Persons FROM [AdventureWorks2017].[Person].[Person] |
Bulk insert operations
The latest method to insert multiple rows in SQL is the BULK INSERT method, which is used to insert data from text files into a SQL table. In case the bulk insert is adequately configurated, this method should guarantee a higher performance than the other methods.
In this method, we should specify the file path in the FROM clause, and the bulk insert operation parameters should be defined in the WITH clause, as shown in the example below:
|
1 2 3 4 5 6 7 8 9 |
BULK INSERT dbo.StagingTable FROM 'C:\PPE.txt' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' ) |
You can learn more about BULK INSERT operations in SQL Server in the following article: BULK INSERT (Transact-SQL) – SQL Server
Summary
In this article, we briefly explained the different approaches used to insert multiple rows in SQL. We explained the INSERT INTO VALUES, the INSERT INTO SELECT, the SELECT INTO FROM, and the BULK INSERT statements. We also listed the different data sources supported in the different SQL commands.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023