Answer

Three tips for using Oracle insert syntax to insert multiple rows

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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:

Expert Discussion

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest