If you use DBMSs such as MySQL or SQL Server, the syntax for inserting multiple rows into a table with a single statement is quite straightforward.

But if you use Oracle Database, you’ll need to use a different syntax.

Option 1: Use a SELECT Query

The first option is to use a SELECT statement for each row that needs to be inserted:

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.

Option 2: Use INSERT ALL

Another option is to use the INSERT ALL statement:

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

Be sure to include the last line selecting from dual.

Option 3: Use Multiple INSERT INTO Statements

Another way to do it is to use INSERT INTO statements:

INSERT INTO Products VALUES ( 1, 'Left Handed Screwdriver', 10.50 );
INSERT INTO Products VALUES ( 2, 'Right Handed Screwdriver', 22.75 );
INSERT INTO Products VALUES ( 3, 'Bottomless Coffee Cup (4 pack)', 15.00 );
INSERT INTO Products VALUES ( 4, 'Urban Dictionary Version 2.3', 75 );
INSERT INTO Products VALUES ( 5, 'Beer Water', 15 );

You might find that this runs a lot slower than the previous two methods if you have a lot of rows to insert.

Option 4: Use SQL*Loader

If you have a lot of rows to insert, and perhaps if you’re doing it regularly, you may want to take a look at SQL*Loader.

SQL*Loader is a utility that enables you to load data from external files into Oracle Database tables.

Using the above examples, the contents of our control file might look something like this:

load data
infile 'products.csv'
into table Products
fields terminated by "," optionally enclosed by '"'              
( ProductId, ProductName, Price )

Where products.csv is the file that contains all the rows to insert.

And then loading the data might look something like this:

sqlldr <username> control=load_products.ctl

Where <username> is our username and load_products.ctl is our control file.

See Oracle’s documentation for SQL*Loader for more information about how to use it.