Posted by Babette Turner-Underwood on Apr 27, 2007
We are assisting one of our clients with a data conversion project and have used a feature that I was surprised so few people knew about. It is the multi-table insert.
Have you ever wanted to select a set of data and then insert different portions of it into different tables? How did you do it? If you are like many developers, you may have opened a cursor loop to select the data, and based on different conditions, used the data in different insert statements.
Let’s assume we are re-architecting our data, and that we have the classic emp and dept tables. We want to put into an exception table for further investigation, any employee whose commission is greater than their salary. In addition, any departments that have no employees should also be placed in exception tables. The old way of doing this might have looked like:
DECLARE
emp_count number;
BEGIN
FOR dept_rec in ( select dept_id, dept_name from dept ) LOOP
emp_count := 0;
FOR emp_rec in ( select emp_id id, emp_name name, sal, comm where dept_id = dept_rec.dept_id ) LOOP
emp_count := emp_count + 1;
IF comm > sal THEN
insert into emp_exception
values ( emp_rec.id, emp_rec.name, emp_rec.sal, emp_rec.comm, emp_rec.dept_id, 'comm greater than sal');
ELSE
insert into new_emp
values ( emp_rec.id, emp_rec.name, emp_rec.dept_id, emp_rec.sal, emp_rec.comm);
END IF;
END LOOP;
IF emp_count = 0 THEN
insert into dept_exception
values ( dept_rec.dept_id, dept_rec.dept_name, 'No employees');
ELSE
insert into new_dept
values ( dept_rec.dept_id, dept_rec.dept_name);
END IF;
END LOOP;
COMMIT;
END:
/
Using outer joins, the select can be converted to, and the code changed to:
DECLARE
emp_count number;
BEGIN
FOR rec in ( select dept.dept_id, dept_name, emp_id id, emp_name name, sal, comm
from dept , emp
where dept.dept_id = emp.dept_id(+)) LOOP
IF rec.id is NULL THEN
insert into dept_exception
values ( dept_rec.dept_id, dept_rec.dept_name, 'No employees');
ELSE
insert into new_dept
values ( rec.dept_id, rec.dept_name);
IF rec.comm > rec.sal THEN
insert into emp_exception
values ( rec.id, rec.name, rec.sal, rec.comm, rec.dept_id, 'comm greater than sal');
ELSE
insert into new_emp
values ( rec.id, rec.name, rec.dept_id, rec.sal, rec.comm);
END IF;
END LOOP;
COMMIT;
END:
/
Okay. I admit the sample code so far is a bit contrived, but I needed something easy and simple to show the logical progression to the multi-table insert. I have broken down the PL/SQL loop into two insert statements. The first replaces the above logic and the second will load the department records, as I want to load only one department record for each department.
INSERT
when ( id is null )
insert into dept_exception
values ( dept_rec.dept_id, dept_rec.dept_name, 'No employees')
when ( comm > sal )
insert into emp_exception
values ( rec.id, rec.name, rec.sal, rec.comm, rec.dept_id, 'comm greater than sal')
when ( first_dept = 'Y')
insert into new_dept
values ( dept_id, dept_name)
when ( nvl(sal,0) >= nvl(comm,0) )
insert into new_emp
values ( id, name, dept_id, sal, comm)
SELECT dept.dept_id, dept_name, emp_id id, emp_name name, sal, comm
from dept , emp
where dept.dept_id = emp.dept_id(+);
INSERT into new_dept
SELECT dept_id, dept_name from dept
MINUS
SELECT dept_id, dept_name from new_dept;
I then tested this with the /*+ APPEND */ hint and set autotrace on. It shows that this also works with NOLOGGING tables and that the amount of redo is considerably reduced (as expected with NOLOGGING). I then created the test tables with CLOBS and confirmed that the multi-table insert also worked with tables with CLOBs.
