Q

Three tips for using Oracle insert syntax to insert multiple rows

If you're looking for insert syntax to insert multiple rows in Oracle, read these tips from three of our community members.

How can I insert multiple rows with a single INSERT syntax in Oracle? Instead of writing five INSERT statements...

to insert five rows into a table, I'd prefer to execute the insertion in a single statement.

According to information on PSOUG.org, an INSERT statement adds one or more records to any single table in a relational database. In order for a user to insert rows into a table, the table must be in the user's own schema or the user must have the INSERT object privilege on the table.

If you are using Oracle 10g and above, community member mrdenny says you can use Insert All to insert multiple rows of data from multiple tables into one table:

INSERT ALL
INTO table (column1, column2, , ,)
VALUES (list of values)
SELECT ....
FROM table1, table2, ,
WHERE....;

If you are inserting data from a spreadsheet or a comma delimited file, mrdenny says, you can create either a temporary table or an external table to load the data from.

Meanwhile, community member FrankKulash offered this suggestion for a multi-row INSERT:

INSERT INTO table_name (column_1, column_2, ..., column_n)
SELECT value_1, value_2, ..., value_n
FROM ...

Alternatively, community member AmitBhuMca suggests inserting multiple rows in a single step using the following Oracle insert syntax:

INSERT ALL INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3') INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3') INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3') SELECT * FROM dual;

According to AmitBhuMca, you can also insert multiple values into multiple tables using a command like this one:

INSERT ALL INTO product (product_id,product_name) VALUES (1000, 'Disc') INTO product (product_id, product_name) VALUES (2000, 'Floppy') INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York') SELECT * FROM dual;

For more information on Oracle inserts:

Note: This tip is a compilation of advice from various experts on our site.

This was last published in November 2013

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

7 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What method do you use to insert multiple rows into an Oracle table?
Cancel
It really depends on things like the database version being used, where the data is coming from, and if it is really necessary to do it in in a single statement (and why). Most methods can be very effective for specific situations.
Cancel
the latest release is 12c by the way, you REALLY should know that
even mentioning v10 (from 2003) makes you look ...
Cancel
 XML integration for transactions to the database in developing this process, my research of resources on the topic throughout the internet, seemed to only discuss primary functions and fundamental application. The following production code is used for inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data. The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can be updated
Cancel
The suggestion by FrankKulash works basically like the first suggestion by mrdenny, and it works on any version of the database. The differences in syntax are the "all" word, the "values" clause, and the fact that if in addition to the values coming from the other tables it is necessary to include fixed values, those would need to be included as literals in the subquery's select list.

The interesting new thing about the INSERT ALL statement is the possibility to insert into more than one table, as shown in AmitBhuMca's last suggestion.
Cancel
really? is the author a DBA? i am guessing 'no'
this is more than useless, it is poorly put together, a collection of 'posts' and therefore can confuse REAL DBAs looking for some SQL advice

poor, poor, poor
Cancel
Use bulk copy insert the data with a series of insert statements, use a parameterized prepared statement from the client, or call a stored procedure with the data passed as a parameter. From that stored procedure, you’ll probably want to insert the data into a table by using statements that insert several rows at a time.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close