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:
INTO 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.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Lauren Soucy
Get an explanation of the different types of Oracle joins, including whether there's really a difference between the LEFT JOIN and LEFT OUTER JOIN ... Continue Reading
Read valuable expert advice about what causes IP address conflicts, and learn how to detect duplicate IP addresses in this tip. Continue Reading
Follow these steps to use Management Studio for SQL 2005 for a successful SQL Server stored procedure backup. Continue Reading