Multitable Insert

Multitable inserts are subject to the following restrictions:

* not for views or materialized views, remote tables, no table collection expression
* all of the insert_into_clauses cannot combine to specify more than 999 target columns.
* are not parallelized if any target table is index organized or has a bitmap index .
* The subquery cannot use a sequence.

Example on Left:

Clear Screen
Drop Table Dept10  Purge;
Drop Table Dept20  Purge;
Drop Table Dept00  Purge;

Create Table Dept10 As Select * From Emp Where 1=2;
Create Table Dept20 As Select * From Emp Where 1=2;
Create Table Dept00 As Select * From Emp Where 1=2;

Insert First When Deptno = 10 Then Into Dept10
       When Deptno = 20 Then Into Dept20
       Else                  Into Dept00
Select *
From Emp;

Set Echo On
Select * from Dept10;
Select * from Dept20;
Select * from Dept00;
Set Echo Off

---

Example on Right:

Drop Table Dept10  Purge;
Drop Table Dept20  Purge;
Drop Table FullPay Purge;

Create Table Dept10 As Select * From Emp Where 1=2;
Create Table Dept20 As Select * From Emp Where 1=2;

Create Table FullPay
(Empno    Number(4)  Not Null,
Sal_Comm Number(7,2)
);

Insert All When Deptno = 10
      And Job   ^= 'PRESIDENT'  Then Into Dept10
     When Deptno = 20
      And Job    = 'CLERK'      Then Into Dept20
     When Comm is not null      Then Into FullPay values(Empno, Sal+Comm)
Select *
From Emp;

Set Echo On
Select * from Dept10;
Select * from Dept20;
Select * from FullPay;
Set Echo Off