Multi table inserts

Unconditional multi table insert:

INSERT ALL
INTO table1 (col1, col2, col3) values (source_table.col1, source_table.col2, source_table.col3)
INTO table2 (col1, col2, col4) values (source_table.col1, source_table.col2, source_table.col4)
INTO table3(col1, col5, col6) values (source_table.col1, source_table.col5, source_table.col6)
SELECT * FROM source_table;

 

Conditional multi table insert:

INSERT ALL
WHEN (sal >= 3000) THEN
INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal)
WHEN (salĀ < 3000 AND sal >= 1500) THEN
INTO mid_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal)
WHEN (sal < 1500) THEN
INTO low_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal)
SELECT * FROM emp;

 

Instead of ‘INSERT ALL’ may by ‘INSERT FIRST’

Restrictions:

  1. You should not use sequences in multi-table insert statements.
  2. You cannot use a multi-table insert to write to a remote table.
  3. You can only use multi-table inserts with tables. Not views, materialized or otherwise.
  4. The sum of all the columns in the various insert-into clauses of a multi-table insert statement must not exceed 999.
  5. You cannot use a TABLE collection expression in a multi-table insert statement.