The Ultimate Guide to Oracle INSERT ALL Statement

Summary: in this tutorial, you will learn how to use the Oracle INSERT ALL statement to insert multiple rows into a table or multiple tables.

In the previous tutorial, you have learned how to insert a row into a table. However, sometimes, you may want to insert multiple rows into a table or multiple tables. In this case, you use the Oracle INSERT ALL statement, which is also referred to as a multitable insert statement.

Oracle provides you with two types of multitable insert statements: unconditional and conditional.

Unconditional Oracle INSERT ALL statement

Insert multiple rows into a table

To insert multiple rows into a table, you use the following Oracle INSERT ALL statement:

INSERT ALL
    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;
Code language: SQL (Structured Query Language) (sql)

In this statement, each value expression val1, val2, or val3 must refer to a column returned by the select list of the subquery.

If you want to use literal values instead of the values returned by the subquery, you use the following subquery:

SELECT * FROM dual;
Code language: SQL (Structured Query Language) (sql)

The following example demonstrates how to insert multiple rows into a table.

First, create a new table named fruits:

CREATE TABLE fruits (
    fruit_name VARCHAR2(100) PRIMARY KEY,
    color VARCHAR2(100) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, use the Oracle INSERT ALL statement to insert rows into the fruits table:

INSERT ALL 
    INTO fruits(fruit_name, color)
    VALUES ('Apple','Red') 

    INTO fruits(fruit_name, color)
    VALUES ('Orange','Orange') 

    INTO fruits(fruit_name, color)
    VALUES ('Banana','Yellow')
SELECT 1 FROM dual;
Code language: SQL (Structured Query Language) (sql)

Third, query data from the fruits table to verify the insertion:

SELECT 
    *
FROM 
    fruits;
Code language: SQL (Structured Query Language) (sql)

As you can see, three rows were inserted into the fruits table successfully as expected.

Insert multiple rows into multiple tables

Besides inserting multiple rows into a table, you can use the INSERT ALL statement to insert multiple rows into multiple tables as shown in the following syntax:

INSERT ALL
    INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;
Code language: SQL (Structured Query Language) (sql)

Conditional Oracle INSERT ALL Statement

The conditional multitable insert statement allows you to insert rows into tables based on specified conditions.

The following shows the syntax of the conditional multitable insert statement:

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
    ELSE
        INTO table_3(column_list ) VALUES (value_list)
Subquery
Code language: SQL (Structured Query Language) (sql)

If you specify the ALL keyword, then Oracle evaluates each condition in the WHEN clauses. If a condition evaluates to true, Oracle executes the corresponding INTO clause.

However, when you specify FIRST keyword, for each row returned by the subquery, Oracle evaluates each condition in the WHEN clause from top to bottom. If Oracle finds a condition that evaluates to true, it executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.

Note that a single conditional multitable insert statement can have up to 127 WHEN clauses.

Conditional Oracle INSERT ALL example

The following CREATE TABLE statements create three tables: small_orders, medium_orders, and big_orders with the same structures:

CREATE TABLE small_orders (
    order_id NUMBER(12) NOT NULL,
    customer_id NUMBER(6) NOT NULL,
    amount NUMBER(8,2) 
);

CREATE TABLE medium_orders AS
SELECT *
FROM small_orders;

CREATE TABLE big_orders AS
SELECT *
FROM small_orders;
Code language: SQL (Structured Query Language) (sql)

The following conditional Oracle INSERT ALL statement inserts order data into the three tables small_orders, medium_orders, and big_orders based on orders’ amounts:

INSERT ALL
   WHEN amount < 10000 THEN
      INTO small_orders
   WHEN amount >= 10000 AND amount <= 30000 THEN
      INTO medium_orders
   WHEN amount > 30000 THEN
      INTO big_orders
      
  SELECT order_id,
         customer_id,
         (quantity * unit_price) amount
  FROM orders
  INNER JOIN order_items USING(order_id);Code language: SQL (Structured Query Language) (sql)

You can achieve the same result by using the ELSE clause in place of the insert into the big_orders tables as follows:

INSERT ALL
   WHEN amount < 10000 THEN
      INTO small_orders
   WHEN amount >= 10000 AND amount <= 30000 THEN
      INTO medium_orders
   ELSE
      INTO big_orders
  SELECT order_id,
         customer_id,
         (quantity * unit_price) amount
  FROM orders
  INNER JOIN order_items USING(order_id);
Code language: SQL (Structured Query Language) (sql)

Conditional Oracle INSERT FIRST example

Consider the following example:

INSERT FIRST
    WHEN amount > 30000 THEN
        INTO big_orders
    WHEN amount >= 10000 THEN
        INTO medium_orders
    WHEN amount > 0 THEN
        INTO small_orders
 SELECT order_id,
         customer_id,
         (quantity * unit_price) amount
 FROM orders
 INNER JOIN order_items USING(order_id);
Code language: SQL (Structured Query Language) (sql)

This statement will not make any sense in an INSERT ALL because the orders whose amount greater than 30,000 would have ended up being inserted into the three tables.

However, with INSERT FIRST, for each row returned by the subquery Oracle will evaluate each WHEN condition from top to bottom:

• First, if the amount of the order is greater than 30,000, Oracle inserts data into the big_tables and ignores the subsequent WHEN conditions.
• Next, if the first evaluation is false and the amount is greater than or equal to 10,000, Oracle inserts data into the medium_tables and it also skips evaluating the third WHEN clause.
• Finally, if the first two WHEN conditions evaluate false, Oracle executes the INTO clause in the ELSE clause which inserts data into the small_orders table.

Oracle INSERT ALL restrictions

The Oracle multitable insert statement is subject to the following main restrictions:

• It can be used to insert data into tables only, not views or materialized views.
• It cannot be used to insert data into remote tables.
• The number of columns in all the INSERT INTO clauses must not exceed 999.
• A table collection expression cannot be used in a multitable insert statement.
• The subquery of the multitable insert statement cannot use a sequence.

In this tutorial, you have learned how to use the Oracle INSERT ALL statement to insert multiple rows into a table or multiple tables.