Oracle / PLSQL: INSERT ALL Statement

This Oracle tutorial explains how to use the Oracle INSERT ALL statement with syntax and examples.

Description

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.

Syntax

The syntax for the INSERT ALL statement in Oracle/PLSQL is:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

Parameters or Arguments

mytable

The table to insert the records into.

column1, column2, column_n

The columns in the table to insert values.

expr1, expr2, ... expr_n

The values to assign to the columns in the table.

Example - Insert into One Table

You can use the INSERT INTO statement to insert multiple records into one table.

For example, 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, 'CISCO')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'GCP')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

This is equivalent to the following 3 INSERT statements:

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'CISCO');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'GCP');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google');

Example - Insert into Multiple Tables

You can also use the INSERT ALL statement to insert multiple rows into more than one table in one command.

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, 'CISCO')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'GCP')
  INTO customers (customer_id, customer_name, city) VALUES (999999, 'James Construction', 'New York')
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, 'CISCO');

INSERT INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'GCP');

INSERT INTO customers (customer_id, customer_name, city) VALUES (999999, 'James Construction', 'New York');