We can use the SQL INSERT statement to insert a row into a table. We can also use it to insert more than one row.

Below are seven ways to insert multiple rows into a table in SQL. Most of these examples should work in the major RDBMSs, with the possible exception of Oracle. But no worries, I’ve included an example just for Oracle.

Use Multiple INSERT Statements

One way to insert multiple rows is to use a separate INSERT statement for each row:

INSERT INTO Pets (PetId, PetTypeId, OwnerId, PetName, DOB)
VALUES ( 1, 2, 3, 'Fluffy', '2020-11-20' );

INSERT INTO Pets (PetId, PetTypeId, OwnerId, PetName, DOB)
VALUES ( 2, 3, 3, 'Fetch', '2019-08-16' );

INSERT INTO Pets (PetId, PetTypeId, OwnerId, PetName, DOB)
VALUES ( 3, 2, 2, 'Scratch', '2018-10-01' );

Here, we inserted three rows into a table called pets. Each row has its own INSERT statement.

Provide All Data in the VALUES Clause

In most of the major RDBMSs (except Oracle), we can pass data for multiple rows in a single VALUES clause:

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');

Here, we used a single INSERT statement, with each row’s data separated by a comma.

Concatenate the Rows with SELECT Statements and UNION Operators

If your DBMS doesn’t support the above method for inserting multiple rows within a single INSERT statement, try this method:

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

Here, we’re selecting each row with a SELECT statement, then using the UNION operator to concatenate that row with the next row.

Insert Multiple Rows in Oracle

The above single-INSERT statement examples won’t work with Oracle Database (at least, not at the time of writing). We can still use multiple INSERT statements to insert multiple rows in Oracle, but if we want to do it in a single INSERT statement, we’ll need to use a different syntax.

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;

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

Use the CREATE TABLE … AS SELECT Statement

We can use the CREATE TABLE ... AS SELECT statement to insert the results of a query into a new table:

CREATE TABLE Pets2 AS
(SELECT * FROM Pets);

This creates a new table called Pets2 (with the same definition as Pets), and inserts the query results into it.

The SQL standard requires parentheses around the subquery clause, but they may be optional in your DBMS (for example PostgreSQL).

Use the SELECT INTO Statement

In DBMSs such as SQL Server and PostgreSQL, we have the option of using the SELECT INTO statement in place of the CREATE TABLE ... AS SELECT statement:

SELECT * INTO Pets2
FROM Pets;

This example does the same as the previous one – it creates a new table called Pets2 and inserts the contents of Pets into it.

MariaDB supports this syntax, but it inserts the result set into a variable. In Oracle, it assigns the selected values to variables or collections. MySQL and SQLite don’t support the SELECT INTO statement at all.

Use the INSERT INTO... SELECT Statement

Another way to insert multiple rows into a table from a query is with the INSERT INTO... SELECT statement:

INSERT INTO Pets2
SELECT * FROM Pets;

However, this method requires that the table already exists. Therefore, before running that code, we would need to create the Pets2 table first.