Multitable Insert in Oracle 9i

Multi-table 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. Its 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 EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;

-- Pivoting insert to split non-relational data
INSERT ALL
NTO Sales_info VALUES (employee_id, week_id, sales_MON)
INTO Sales_info VALUES (employee_id, week_id, sales_TUE)
INTO Sales_info VALUES (employee_id, week_id, sales_WED)
INTO Sales_info VALUES (employee_id, week_id, sales_THUR)
NTO Sales_info VALUES (employee_id, week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
Sales_WED, sales_THUR, sales_FRI
FROM Sales_source_data;

-- 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)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id 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 ('%00%') THEN
INTO hiredate_history_00 VALUES (DEPTID, HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES (DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES (DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM (salary) SAL,
MAX (hire_date) HIREDATE
FROM employees GROUP BY department_id;
The restrictions on multi-table Inserts are:
Multi-table inserts can only be performed on tables, not on views or materialized views.
You cannot perform a multi-table insert via a DB link.
You cannot perform multi-table inserts into nested tables.
The sum of all the INTO columns cannot exceed 999.
Sequences cannot be used in the subquery of the multi-table insert statement.