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)
     ( deptno, ename, job)
when job='MANAGER'
then into hrm_managers
     ( id, name, dpt_id)
     ( empno, ename, deptno)
     into hrm_employees
     ( id, name, job, dpt_id)
     ( empno, ename, job, deptno)
     into hrm_salaries
     ( epe_id, salary, commission)
     ( 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.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.