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 first published in November 2013

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.
Related Discussions

Lauren Soucy asks:

What method do you use to insert multiple rows into an Oracle table?

3  Responses So Far

Join the Discussion

3 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close