Oracle Multi - Table Inserts

Many a times we forget the power of SQL and resort to PL/SQL for simple requirements. Conditional insert is one such example which can be handled easily by Multi-Insert SQL queries as against writing PL/SQL blocks. Sample:

INSERT

WHEN (<condition>) THEN

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

WHEN (<condition>) THEN

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

ELSE

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

 

Check out below sample taken from http://www.pythian.com/news/463/oracles-little-known-multi-table-insert by Babette Turner-Underwood

 

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(+);

 

A variation of INSERT WHEN is INSERT ALL which unconditionally inserts into all tables specified.

INSERT ALL

INTO <table_name> VALUES <column_name_list>

INTO <table_name> VALUES <column_name_list>

...

SELECT Statement;