Tuesday, August 26, 2008

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

No comments:

Post a Comment