Multitable Inserts feature

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.

-- Unconditional insert into ALL tables

INSERT ALL

  INTO sal_history VALUES(empid,hiredate,sal)

  INTO mgr_history VALUES(empid,mgr,sysdate)

SELECT employee_id AS empid,

       hire_date AS hiredate,

       salary AS sal,

       manager_id AS mgr

FROM   employees

WHERE  employee_id > 200;

-- Conditionally insert into ALL tables

INSERT ALL

  WHEN sal > 10000 THEN

    INTO sal_history VALUES(empid,hiredate,sal)

  WHEN mgr > 200 THEN

    INTO mgr_history VALUES(empid,mgr,sysdate)

  WHEN 1=1 THEN

    INTO full_history VALUES(empid,mgr,sysdate)

SELECT employee_id AS empid,

       hire_date AS hiredate,

       salary AS sal,

       manager_id AS mgr

FROM   employees

WHERE  employee_id > 200;

-- Insert into the FIRST table with a matching condition

INSERT FIRST

  WHEN sal > 25000  THEN

    INTO special_sal VALUES(deptid,sal)

  WHEN hiredate LIKE ('%') THEN

    INTO hiredate_history_00 VALUES(deptid,hiredate)

  WHEN hiredate LIKE ('%99%') THEN

    INTO hiredate_history_99 VALUES(deptid,hiredate)

  ELSE

    INTO hiredate_history_not_99 VALUES(deptid, hiredate)

SELECT department_id AS deptid,

       SUM(salary) AS sal,

       MAX(hire_date) AS hiredate

FROM   employees GROUP BY department_id;

The restrictions on multitable inserts are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.