How to PROPERLY insert multiple rows in SQL? [SOLVED]

Welcome to this comprehensive guide on how to insert multiple rows in SQL! If you have ever worked with databases, you are probably familiar with the Structured Query Language (SQL) and how it is used to interact with relational databases. One of the essential tasks when working with databases is inserting data, and in this tutorial, we will focus on a powerful technique to do just that: SQL insert multiple rows. This approach allows you to add multiple records to a table in a single query, saving time and improving performance. Whether you're a seasoned developer or just getting started with databases, this guide will walk you through the process step-by-step, covering syntax, examples, and best practices. Let's dive in and learn the ins and outs of sql insert multiple rows!

 

Different methods to insert multiple rows in SQL

There are several methods to insert multiple rows in SQL, depending on the SQL dialect you are using and your specific requirements. Here, we will discuss some of the common methods:

• Multiple VALUES clauses: Insert multiple rows in a single INSERT statement by using multiple VALUES clauses.
• UNION ALL with SELECT: Combine multiple SELECT statements using UNION ALL to insert multiple rows.
• INSERT ... SELECT: Use a single INSERT statement with a SELECT query to insert multiple rows from another table or query result.
• Bulk insert methods: Leverage database-specific bulk insert methods, like BULK INSERT in SQL Server or LOAD DATA INFILE in MySQL, to insert multiple rows at once.
• Prepared statements with batch processing: Use prepared statements to execute the same INSERT statement multiple times with different values, improving performance.
• Stored procedures or functions: Create a stored procedure or function that inserts multiple rows by accepting a set of values as input parameters.
• Third-party tools or libraries: Utilize external tools or programming libraries that provide bulk-insert functionality for specific database systems.

In this tutorial we will cover using SQL Insert statement and using SQL Insert-select-union-all methods with syntax and examples.

 

Lab Environment

Consider student result management database to perform practical examples on SQL insert multiple rows

Student Table

 

Method-1: Using SQL Insert statement

With an SQL Insert statement, new rows of data are added to a table. If the INSERT statement executes correctly, it returns the number of rows inserted into the table. We can insert multiple rows into a table using the SQL Insert statement.

SQL insert statement is used to single record in a table but we can inject multiple records in a table using single insert statement.  To insert multiple records we need to specify multiple values separated by comma for each new row.

The maximum number of rows we can insert using this INSERT statement is 1,000. BULK INSERT or multiple INSERT statements are recommended if you want to insert more rows than that.

Syntax:

insert into table_name(column 1,column2,column3...)
 values ( value 1,value 2,value3,...) , 
( value 1,value 2,value3,...),
( value 1,value 2,value3,...),
.......... ;

Here,

• table_name refers to the name of the table where you want to insert the data. Replace it with the actual table name you're working with.
• (column1, column2, column3...) specifies the names of the columns in the table where you want to insert data. Replace these with the actual column names you're targeting.
• VALUES (value1, value2, value3...) represents the specific values you want to insert into the corresponding columns. Make sure the order of the values matches the order of the columns you specified earlier.
• You can insert multiple rows at once by separating them with commas. Each set of values within parentheses represents a single row of data.
• After listing all the rows you want to insert, conclude the statement with a semicolon (;) to mark the end of the SQL command.

 

Example-1: SQL Insert multiple rows using insert statement with values of all fields

Write SQL query to insert 3 rows in student table using one insert statement

INSERT INTO tblstudent
VALUES  (109, 'krina', '20014', '4/2/2002', 'e16200014', '2/3/1992', 'krina@gmail.com', 'Surat', 4) , 
(110, 'komal', '20015', '4/2/2002', 'e16200015', '2/4/1992', 'komal@gmail.com', 'Vapi', 4) ,  
(111, 'shiva', '20016', '4/2/2002', 'e16200016', '2/5/1992', 'krina@gmail.com', 'Vapi', 4);

• In the above query, SQL insert statement is used to insert multiple rows into student table
• Each row values are separated by comma

OUTPUT:

To view the output of above executed query, we need to use SQL Select statement

SELECT  student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM   tblstudent;

 

Example-2: SQL Insert multiple rows using insert statement with values of specified fields

Write SQL query to insert two rows with values of specified fields in the student table using SQL insert statement

insert into tblstudent (student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth,city, class_id) 
values  (112, 'mansi', '20017', '4/2/2002', 'e16200017', '3/3/1992',  'vadodara', 4) , 
(113, 'laxmi', '20018', '4/2/2002', 'e16200018', '4/4/1992', 'Vapi', 4) ;

• In the above query, SQL insert statement is used to insert two rows with specified filed values.
• Each row values are separated by comma.

Note : To insert value in specified fields with SQL insert statement we need to specified field name with table name.

OUTPUT:

To view the output of above executed query, we need to use SQL Select statement

SELECT  student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM   tblstudent;

 

Method-2: Using SQL Insert-select-union all

SQL UNION ALL command is used to combines the result set of two or more SELECT statements. To insert multiple rows using SQL insert-select-union we need to use multiple SQL select statement, combine all select statements using union all command and inject combined statement into SQL insert statement.

Syntax:

INSERT INTO table_name(column_name,….)
SELECT value1,value2,…
UNION ALL   
SELECT value1,value2,..
UNION ALL  
SELECT value1,value2,..;

Here,

• table_name refers to the name of the table where you want to insert the data. Replace it with the actual table name you're working with.
• (column_name, ...) specifies the names of the columns in the table where you want to insert the data. Replace these with the actual column names you're targeting.
• The SELECT statement is used to retrieve data from one or more tables or views. You can select specific values or perform calculations or transformations within the SELECT statement.
• value1, value2, ... represent the specific values you want to insert into the corresponding columns. These values can be retrieved from the SELECT statement or calculated using expressions.
• UNION ALL is used to combine the result sets of multiple SELECT statements into a single result set. It appends the rows from each SELECT statement to the result set, including duplicate rows.
• You can have multiple SELECT statements following the initial SELECT statement, separated by the UNION ALL keyword. Each SELECT statement represents a different set of values to be inserted into the table.
• After listing all the SELECT statements, conclude the statement with a semicolon (;) to mark the end of the SQL command.

 

Example-1: SQL insert-select-union all without where condition

Write SQL query to insert 3 new rows in a student table using union all

INSERT INTO tblstudent
SELECT        109 AS id, 'krina' AS name, '20014' AS admissionno, '4/2/2002' AS admissiondate, 'e16200014' AS enrollmentno, '2/3/1992' AS dateofbirth, 'krina@gmail.com' AS email, 'Surat' AS city, 4 AS classid
UNION ALL
SELECT        110 AS id, 'komal' AS name, '20015' AS admissionno, '4/2/2002' AS admissiondate, 'e16200015' AS enrollmentno, '2/4/1992' AS dateofbirth, 'komal@gmail.com' AS email, 'Vapi' AS city, 4 AS classid
UNION ALL
SELECT        111 AS id, 'shiva' AS name, '20016' AS admissionno, '4/2/2002' AS admissiondate, 'e16200016' AS enrollmentno, '2/5/1992' AS dateofbirth, 'krina@gmail.com' AS email, 'Vapi' AS city, 4 AS classid

• In the above query, SQL union all command is applied with SQL select to combine result of all select statement.
• Each select statement return new row values for all fields.
• SQL insert statement will insert combined result of all select statement.

OUTPUT:

To view the output of above executed query, we need to use SQL Select statement

SELECT  student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM    tblstudent;

 

Example-2: SQL insert-select-union all with where condition

Write SQL query to conditionally insert two rows in student table using union all command

INSERT INTO tblstudent
SELECT 114 AS id, 'mahesh' AS name, '20018' AS admissionno, '5/4/2002' AS admissiondate, 'e16200018' AS enrollmentno, '7/1/1992' AS dateofbirth, 'mah@gmail.com' AS email, 'Surat' AS city, 4 AS classid
FROM tblstudent
WHERE(enrollmentno = 'e16200018')
UNION ALL
SELECT 113 AS id, 'suresh' AS name, '20019' AS admissionno, '5/6/2002' AS admissiondate, 'e16200019' AS enrollmentno, '4/6/1992' AS dateofbirth, 'sur@gmail.com' AS email, 'Vapi' AS city, 4 AS classid
FROM  tblstudent AS tblstudent_1
WHERE (enrollmentno = ' e16200019')

• In the above query, SQL union all command is applied with SQL select to combine result of two select statement.
• Each select statement will check for condition and return new row values for all fields.
• SQL insert statement will insert combined result of all select statement.

OUTPUT:

To view the output of above executed query, we need to use SQL Select statement

SELECT   student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM  tblstudent;

 

Summary

In the above article of insert multiple rows; we have covered overview of SQL insert statement, list out two methods to insert multiple rows in a table, explain both methods insert multiple rows using insert command and insert-select-union all with syntax and practical examples.

 

Further Reading

learn.microsoft.com: SQL INSERT
Inserting multiple rows in a single SQL query? - Stack Overflow