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

Lucas Jellema 2
0 0
Read Time:2 Minute, 10 Second

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.

About Post Author

Lucas Jellema

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.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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

The 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 […]
%d bloggers like this: