Multi-Table Inserts -- Just the Basics

You can use a multi-table insert when data from one table can be used to populate several tables. This is something often seen in a data warehouse.

They can be unconditional or conditional, but not both. To do both, you can 'fake' it out by adding 'When 1=1 Then' to unconditional inserts.

If you want to see this work, you can copy the code shown below. It will run in the Scott schema.

• Unconditional multi-table insert inserts all rows selected in the subquery into all of the table insert clauses.
• Conditional mutli-table insert: you can specify ALL (default) or FIRST.
• ALL - evaluates each WHEN
• FIRST - execute the first WHEN (top to bottom), and then none below it.
• . With the FIRST statement, you can have an ELSE clause to perform if no WHENs are true.
• If there is no ELSE, no action is performed on that row.

conn Scott/Tiger
----------------------------------------
-- Drop these tables if already created.
----------------------------------------
Drop Table Sal_Hist;
Drop Table Big_Sal;

------------------------------------
-- Create empty tables to hold data.
------------------------------------
CREATE TABLE Sal_Hist 
AS 
SELECT EmpNo , Job , Sal 
  FROM Emp 
  WHERE 1 = 2; 
  
CREATE TABLE Big_Sal  
AS 
SELECT EmpNo , Sal 
  FROM Emp 
 WHERE 1 = 2; 

------------------------------------
-- Conditional Mutli-Table Insert
------------------------------------
Insert All
  When 1 = 1 Then  -- always insert
  Into Sal_Hist
  Values (EmpNo, Job, Sal)
-----------------------------------------
  When Sal >= 3000 Then -- conditionally insert
  Into Big_Sal
  Values (EmpNo, Sal)
-------------------------------------------
Select EmpNo , Job , Sal  -- These are the columns pulled
  From Emp                -- that can potentially be inserted 
 Where DeptNo = 20;       -- in the newly-created tables.

------------------------------------
-- check to see what got inserted
------------------------------------
Select Count(*) 
  from Emp
 Where DeptNo = 20;
 
Select * From Sal_Hist;
Select * From Big_Sal;
  
------------------------------------
-- Delete to try insert #2
------------------------------------
Delete From Sal_Hist;
Delete from Big_Sal;

------------------------------------
-- Unconditional Mutli-Table Insert
-- (Just remove the 'WHEN' clauses)
------------------------------------
Insert All
  Into Sal_Hist
  Values (EmpNo, Job, Sal)
-----------------------------------------
  Into Big_Sal
  Values (EmpNo, Sal)
-------------------------------------------
Select EmpNo , Job , Sal
  From Emp
 Where DeptNo = 20;

------------------------------------
-- check to see what got inserted
------------------------------------
Select * From Sal_Hist;
Select * From Big_Sal;

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