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.
1 Comment. Leave new
Andrew Clarke riffs on this post and adds a lot of useful thoughts on it at his blog https://radiofreetooting.blogspot.com/2007/05/another-use-for-insert-all-syntax.html