When working with SQL, we can use the INSERT statement to insert a new row into a table. But what if we want to insert more than one row?

Fortunately, we can still use the INSERT statement. Below is a quick example that inserts multiple rows using SQL.

Syntax

In many RDBMSs, we can use the following syntax to insert multiple rows:

INSERT INTO table (col1, col2, ...)
VALUES
    (value1, value2, ...),
    (value1, value2, ...),
    (value1, value2, ...),
    ...;

Here, col1, col2, ... represents the column names, and value1, value2, ... represents the values that go into those columns. The values must be in the same order as the column names.

Oracle doesn’t support this syntax, but see below for Oracle.

Example

Here’s an example of using the above syntax to insert multiple rows into a table called pets:

INSERT INTO pets (PetId, PetTypeId, OwnerId, PetName, DOB)
VALUES
    (1, 2, 3, 'Fluffy', '2020-11-20'),
    (2, 3, 3, 'Fetch', '2019-08-16'),
    (3, 2, 2, 'Scratch', '2018-10-01');

Now when we run a simple SELECT statement against the table, we can see the three rows that we just inserted:

SELECT * FROM pets;

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     3 |         2 |       2 | Scratch | 2018-10-01 |
+-------+-----------+---------+---------+------------+
3 rows in set (0.00 sec)

This example should work in most of the major RDBMSs including PostgreSQL, SQLite, SQL Server, MySQL, etc. But it won’t work in Oracle Database. See below for an Oracle example.

Insert Multiple Rows in Oracle

The above example won’t work with Oracle Database (at least, not at the time of writing). We need to use a different syntax when working with Oracle.

Here’s an example of inserting multiple rows in Oracle:

INSERT INTO Products (ProductId, ProductName, Price) 
    WITH p AS ( 
        SELECT 1, 'Left Handed Screwdriver', 10.50 FROM dual UNION ALL 
        SELECT 2, 'Right Handed Screwdriver', 22.75 FROM dual UNION ALL 
        SELECT 3, 'Bottomless Coffee Cup (4 pack)', 15.00 FROM dual UNION ALL 
        SELECT 4, 'Urban Dictionary Version 2.3', 75 FROM dual UNION ALL
        SELECT 5, 'Beer Water', 15 FROM dual
  )
SELECT * FROM p;

We need to include FROM dual for each row, UNION ALL to combine each SELECT statement, as well as the final SELECT statement.

This is just one of at least four ways we can insert multiple rows in Oracle.

More Options for Inserting Multiple Rows in SQL

The above examples aren’t the only way of inserting multiple rows in SQL. We have a number of options available to us. It all depends on what we need to do. See 7 Ways to Insert Multiple Rows in SQL for examples.