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.
table (column1, column2, , ,)
VALUES (list of values)
FROM table1, table2, ,
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
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.
This was first published in November 2013