0 
0 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:
- 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
- HRM_MANAGERS with records for the employees in EMP that have the job MANAGER
- HRM_SALARIES that contains Salary details for all non-Manager employees
- 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.
See Duncan Mill’s post on the quiz Alex and myself did on ODTUG: http://www.groundside.com/blog/content/DuncanMills/2006/06/20/Quiztastic.html. He is rather nice about us.
Hi Lucas, ist unbelievable – found documentation link : http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2079970
Greetings
Karl