Multi-Table Inserts

Oracle has many useful extensions to the SQL programming language.  One of them is the multi-table insert statement used primarily in data warehousing.  There are 2 forms of the multi-table insert, unconditional and conditional.  The unconditional insert allows you to insert data into multiple tables using one DML statement. An example is depicted below:

INSERT [ALL]
                 INTO clause1
                 INTO clause2
SELECT FROM column1,column2,...
FROM source_table;

Here, the multi-table insert is unconditional and derived from the row-set returned from the evaluation of a subquery of one or more tables. A conditional multi-table insert uses conditions to insert data into multiple tables. If the multiple conditions are true, each insert is processed.  An example of the syntax is shown below:

INSERT [ALL][FIRST]
          WHEN condition1 THEN
                            INSERT INTO clause
          WHEN ELSE
                            INSERT INTO clause
SELECT column1,column2,...
FROM source_table;

If you specify ALL(the default), the database evaluates each WHEN clause in the order in which it appears in the statement. For each WHEN clause that evaluates to true, the database executes the INSERT INTO clause.

If you specify FIRST, the database executes the INSERT INTO for first WHEN clause that evaluates to true, and skips the subsequent WHEN clauses for the given row.  If no WHEN clauses evaluate to true, the database executes the INSERT INTO clause associated with the ELSE clause for a given row.

Per the Oracle extension, multi-table inserts can only performed on tables, not views.  In addition, you cannot insert into a remote table.  Nonetheless, multi-table inserts can be a valuable tool in data warehousing transactions.