How to insert multiple rows in SQL? [SOLVED]

Different methods to insert multiple rows

• SQL Insert statement
• SQL Insert-select-union all

 

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 of SQL insert statement to insert multiple rows

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

 

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 of SQL insert-select-union all to SQL insert multiple rows

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

 

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.

 

Reference

SQL insert

 

Further Reading

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