Oracle’s Little-Known Multi-Table Insert

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.