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;