Insert multiple rows with a single INSERT statement

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?

Answer: You can insert multiple rows using the following 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;

Example #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Example #2

You can also insert multiple rows into multiple tables. For example, 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, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;

This example will insert 2 rows into the suppliers table and 1 row into the customers table.