Multi-table INSERT

In Oracle SQL, a multi-table insert is used when you want to insert data into more than one table using a single INSERT statement. This can be achieved using the INSERT ALL statement. Here's the basic syntax: 

INSERT ALL
    INTO table1 (column1, column2, ...)
    INTO table2 (column1, column2, ...)
    INTO table3 (column1, column2, ...)
    ...
Subquery; 

Here's a breakdown of the syntax elements:

• INSERT ALL: This signals the start of a multi-table insert.
• INTO table1 (column1, column2, ...): Specifies the first target table and the corresponding column-value pairs.
• INTO table2 (column1, column2, ...): Specifies the second target table and its column-value pairs.
• INTO table3 (column1, column2, ...): Specifies the additional target tables and their respective column-value pairs.
• Subquery: This is a SELECT statement that provides rows for the INSERT ALL statement.

The multi-table INSERT statement is a variation on the INSERT statement syntax. A multi-table INSERT statement repeats the INTO clause of the INSERT statement to insert data into more than one table. Each INTO clause applies to just one table, but by repeating it, you can add data to multiple tables at a time. 

The multi-table INSERT must have a subquery to select rows for inserting. Data retrieved by the subquery will be inserted into each table listed in each table in the INTO clause. That is, the enumerated columns in each INTO clause and the rows retrieved by the subquery must corresponds to each other. To illustrate, let's consider a scenario shown in the figure below:

In the figure above, I created three tables with identical columns. Then we're trying to insert id and name values from the pokemon table. To achieve this, we can write a query as follows:

In line 22, I created a query selecting id and name values from the pokemon table. Then each INTO clause in line 19, 20, and 21 inserts the entire row from the subquery into each table. Thus, records stored in the newly created three tables are identical in this example.

Multi-table INSERT statements can accomplish a variety of tasks, including the following:

• Support logical archiving at any level of detail with logical decision points embedded in the INSERT statements.
• Query data from one table and insert the data into multiple tables with conditional logic, such as transforming data into a series of archive.
• Exchange data between two similar systems of different requirements - perhaps between a transaction-based application and a data warehouse optimized for analysis.
• Integrate complex queries with GROUP BY, HAVING, set operators, and more, all while moving any number of rows dynamically, distributing output into multiple data targets, and programming logical decision points to control data distribution.
• Transform data that is stored in rows and levels into a cross-tabulation output, the type you would typically see in a spreadsheet.

What we have seen in the example mentioned earlier is the unconditional multi-table INSERT statement. To fully support the tasks, you can also add conditional expressions to the query. This is called the conditional multi-table INSERT statement. The basic syntax of the conditional multi-table INSERT statement is:

INSERT {ALL | FIRST}
    WHEN expression THEN
        INTO table1 VALUES (column1, column2, ...)
    WHEN expression THEN
        INTO table2 VALUES (column1, column2, ...)
    WHEN expression THEN
        INTO table3 VALUES (column1, column2, ...)
    ...
    ELSE
        INTO tableN VALUES (column1, column2, ...) 
Subquery; 

Here's a breakdown of the syntax elements:

• INSERT {ALL | FIRST}: Specifies whether to insert all rows or stop after condition is met.
• WHEN expression THEN: Conditional clause indicating the condition that must satisfied for the subsequent INTO clause to be executed. 
• INTO tableX VALUES (column1, column2, ...): Specified the target table (tableX) and the values to be inserted into the specified columns.
• ELSE INTO tableN VALUES (column1, column2, ...): An optional ELSE clause for a default action if none of the previous conditions is true. it specifies the target table (tableN) and the values to be inserted into the specified columns.
  
• Subquery: The actual data source for the INSERT statement. 

To illustrate, let's consider a scenario depicted in the figure below:

In the SQL statement shown above, the INSERT FIRST specifies that the insert operation should stop after the first condition is met. That is, as soon as the condition specified in the first WHEN clause is evaluated to be true, Oracle will insert rows from the subquery into the corresponding table and skip the subsequent conditions. If I have used ALL instead, Oracle would have continued to evaluate every WHEN conditions, regardless of the result from each clause.

In the WHEN clauses, the conditions checks if the type1 or type2 column of the subquery has certain value. If that's the case, then Oracle will insert the id and name values selected from the subquery into the corresponding table. If none of the previous condition is true, then the ELSE clause is executed, and it inserts the values to the ash_pokemon table.