The minimum number of statements required for Inserting Records from Two Source Tables into Four Target Tables – Introducing Multi Table Insert

It's Showtime! Today at ODTUG 2006, Alex and I are presenting our Oracle Quiz on SQL and PL/SQL – the water is still burning. We will present our audience with 15 questions on SQL and PL/SQL features and functions. Even though most questions are on core database features, many of the will still have a surprise element. Or at least that is our assumption. We will let you know how the audience fared and who won the prize.

To give you some idea about the type of question we will be asking, I will present one of the questions in this article. It is introduced with a simple picture:

The situation is like this:
we have the well known source tables EMP and DEPT. We also have four target tables in our new database design:

1. HRM_EMPLOYEES that holds records for all normal employees – but not the managers and only a few of the columns currently found in table EMP
2. HRM_MANAGERS with records for the employees in EMP that have the job MANAGER
3. HRM_SALARIES that contains Salary details for all non-Manager employees
4. HRM_DEPARTMENTS that contains records for all departments

The question put to the audience in the quiz is: What is the smallest number of statements needed for moving the data from EMP and DEPT to these four target tables. Do you need four statements? Can you manage in three? Do you need two insert statements, one for each source table? Or can you perform this data migration in a single statement?

After allowing them some time to think, Alex will demand an answer to be picked… So you choose your answer now as well…

In this case, the most extreme answer is the correct one: using a Multi-Table insert statement, you can perform this operation with a single statement. It goes something like:

insert first
when empno = -1
then into hrm_departments
     ( id, name, city)
     values
     ( deptno, ename, job)
when job='MANAGER'
then into hrm_managers
     ( id, name, dpt_id)
     values
     ( empno, ename, deptno)
else
     into hrm_employees
     ( id, name, job, dpt_id)
     values
     ( empno, ename, job, deptno)
     into hrm_salaries
     ( epe_id, salary, commission)
     values
     ( empno, sal, comm )
select empno, ename, job, deptno, sal, comm
from   emp
union all
select -1, dname, loc, deptno, null, null
from   dept
 

Executing this single statement will have 29 records created in four different target tables! 

You can download the script to set up the four target tables and do the insert to try it out for yourself: mti.sql.