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

Lucas Jellema 2

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.

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

Comments are closed.

Next Post

Great Presentation on migrating Oracle Forms to ADF Faces at ODTUG 2006

Facebook0TwitterLinkedinThe title of this article is hugely misleading. Or somewhat at least. The presentation has to take place yet. And it is my own – so how great can it be? So why the noisiness? It’s relieve mainly. After struggling with my demonstration for some time, I now have them […]