The Oracle INSERT ALL statement is used to add multiple rows with a single INSERT statement. The rows can be inserted into one table or multiple tables using only one SQL command. The syntax for the INSERT ALL statement in Oracle/PLSQL is:
INSERT ALL
INTO yourtable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO yourtable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO yourtable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;
Example - Insert into One Table
If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:
INSERT ALL
INTO suppliers VALUES (1000, 'Yahoo')
INTO suppliers VALUES (2000, 'Facebook')
INTO suppliers VALUES (3000, 'Google')
SELECT * FROM dual;
This is equivalent to the following 3 INSERT statements:
INSERT INTO suppliers VALUES (1000, 'Yahoo');
INSERT INTO suppliers VALUES (2000, 'Facebook');
INSERT INTO suppliers VALUES (3000, 'Google');
Example - Insert into Multiple Tables
If you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'Yahoo')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Facebook')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'James Bond', 'London')
SELECT * FROM dual;
This example will insert 2 rows into the suppliers table and 1 row into the customers table. It is equivalent to running these 3 INSERT statements:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'Yahoo');
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Facebook');
INSERT INTO customers (customer_id, customer_name, city) VALUES (999999, 'Jame Bond', 'London');