How to Use Multitable INSERT ALL in Oracle

Loading data often means sending rows to different tables by their content: long-haul and short-haul routes, valid and suspect records, current and archived orders. A multitable INSERT reads the source query once and inserts each row into one or more tables, unconditionally or by conditions, in a single statement.

Code for This Guide

The main examples are in the examples/dml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

insert all
  [when condition then] into table [(columns)] values (exprs)
  [when condition then] into table ...
  [else into table ...]
subquery

insert first
  when condition then into table ...
  [when ...] [else into table ...]
subquery

INSERT ALL evaluates every WHEN clause for every row, so a row can go into several tables. INSERT FIRST stops at the first WHEN clause that is true.

Unconditional INSERT ALL

Without WHEN clauses, every source row goes into every INTO clause, which turns columns into rows: here each route's origin and destination become two rows.

Example:

-- unconditional INSERT ALL: each source row goes into every INTO clause
insert all
  into route_endpoints values (route_id, origin, 'ORIGIN')
  into route_endpoints values (route_id, destination, 'DESTINATION')
select route_id, origin, destination from routes where route_id <= 2;

select route_id, airport, role from route_endpoints order by route_id, role desc;

Output:

4 rows inserted.

   ROUTE_ID AIRPORT    ROLE
___________ __________ ______________
          1 DXB        ORIGIN
          1 LHR        DESTINATION
          2 LHR        ORIGIN
          2 DXB        DESTINATION

Two routes produce four rows.

Conditional INSERT ALL

Routes of 7,000 km or more go to LONG_HAUL, and those under 4,000 km to SHORT_HAUL; routes in between go nowhere.

Example:

insert all
  when distance_km >= 7000 then
    into long_haul  values (route_id, origin, destination, distance_km)
  when distance_km <  4000 then
    into short_haul values (route_id, origin, destination, distance_km)
select route_id, origin, destination, distance_km from routes;

select (select count(*) from long_haul)  as long_haul,
       (select count(*) from short_haul) as short_haul;

Output:

30 rows inserted.

   LONG_HAUL    SHORT_HAUL
____________ _____________
          16            14

The work tables are created empty from ROUTES with WHERE 1 = 0 before the insert, and dropped afterward.

INSERT FIRST

INSERT FIRST puts each row into the first matching band only, and ELSE catches the rest.

Example:

insert first
  when distance_km >= 10000 then into route_bands values ('ULTRA', route_id)
  when distance_km >= 5000  then into route_bands values ('LONG', route_id)
  else                           into route_bands values ('SHORT', route_id)
select route_id, distance_km from routes;

select band, count(*) as routes from route_bands group by band order by routes;

Output:

50 rows inserted.

BAND        ROUTES
________ _________
ULTRA           14
SHORT           16
LONG            20

With INSERT ALL, a route of 12,000 km would have gone into both ULTRA and LONG. INSERT FIRST makes the bands exclusive.

Things to Know

• The source query runs once, which is faster than one INSERT ... SELECT per target table.
• The statement is atomic: if any row fails in any table, the whole statement is rolled back.
• The VALUES clauses can use any column of the subquery, and the targets need not have the same columns.

Related Guides

• How to Insert Data into a Table in Oracle 23ai
• How to Turn Columns into Rows with UNPIVOT in Oracle

Conclusion

Multitable inserts send the rows of one query to several tables in one statement: INSERT ALL into every matching target, INSERT FIRST into the first match, with ELSE for the rest. Use them to split and route data during loads with one pass over the source.