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 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:

• Oracle.com offers help documentation on multi-row inserts
• Find a script that generates Oracle INSERT statements
• Learn how to copy a table into another table in Oracle
• Discover an automatic method of inserting records into Oracle

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