How to Insert Multiple Rows in SQL?

In the INSERT INTO statement tutorial, we have seen how to enter new records into the database tables.

You may use column names with VALUES or without using the column names for inserting records.

In many scenarios, you may require entering multiple rows by using a single query of SQL.

In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.

First way – Separating the VALUES part by a comma

As using simple INSERT INTO statement, we used this statement for entering a single record (for our example employee table):

INSERT INTO sto_employees

(id, emp_name, emp_salary, emp_age, join_date)

VALUES

(13, 'Shaan', 5000.00, 35, '2016-12-13');

For entering multiple records, separate the VALUES by commas. See the example below:

INSERT INTO sto_employees

(id, emp_name, emp_salary, emp_age, join_date)

VALUES

(14, 'Jumma', 4500, 35, '2017-09-15'),

(15, 'Hareem', 3600, 26, '2018-05-29'),

(16, 'Rudaba', 3200, 24, '2016-11-04');

The above query will enter multiple records into the sto_employees table.

You can see, after the VALUES keyword, the first row values are enclosed in parenthesis.

This is followed by a comma and then second row values and so on.

Insert multiple records without specifying the column names

Multiple records can also be entered without providing the column names (just like in the INSERT INTO command with the single record).

In that case, you have to provide all column name values in the same sequence as in the actual physical table.

Otherwise, an error may be raised if fewer values are provided than the number of columns in the table or if data type mismatch.

The following query inserts three records in the employee’s table without using the column names:

INSERT INTO sto_employees

VALUES

(17, 'Mike', 4500, 35, '2017-09-15', '012-1454-457474'),

(18, 'Jameel', 3600, 26, '2018-05-29', '005-5774-457474'),

(19, 'Janaan', 3200, 24, '2016-11-04', '001-4747-457474');

Again, this query is tested on MS SQL Server and MySQL database.

Second Way – Using INSERT INTO & SELECT statements

The combination of INSERT INTO and SELECT statements also allows you to enter multiple records by a single query. However, this is used to copy data from one table to another.

• The source table (from where you are copying data) is specified in the SELECT statement.
• The target table (where you are inserting records) is specified in the INSERT INTO command.

The following example shows an example query:

INSERT INTO employees_sub

SELECT * FROM sto_employees;

In that case, the complete table rows of sto_employees will be inserted into the employee_sub table.

The columns’ sequence and specification must be the same (names can be different).

You may also use the WHERE clause in the SELECT statement for inserting specific rows.

Third way: The UNION Operator

In the third way of inserting multiple rows by a single query is using the UNION operator.

So, again this technique uses INSERT INTO and SELECT statements, however rather than copying the data from one table to another, you may insert data with independent values (just like the first way).

The INSERT INTO specifies the target table while in the SELECT statements, values are specified.

Multiple SELECT statements are combined by the UNION operator as shown in the query below:

INSERT INTO [sto_employees]

 ([id]

 ,[emp_name]

 ,[emp_age]

 ,[emp_salary]

 ,[join_date]

 ,[phone])

SELECT 20, 'Tim', 45, 5500, '2017-09-15','044-4745-145745'

UNION ALL

SELECT 21, 'Tony', 47, 6000, '2017-09-15','092-1545-474184'

UNION ALL

SELECT 22, 'Tania', 34, 4500, '2017-09-15','056-2455-454745';

The result-set before and after executing this query (for demo only):