Table of Contents
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
Further Reading
learn.microsoft.com: SQL INSERT
Inserting multiple rows in a single SQL query? - Stack Overflow