Multitable Insert.

Multi table insert seems to be a pretty cool method of inserting into multiple tables while selecting from a single source table. Following are some examples that i tried out.

create table emp_exec as
select *
from emp
where 1=3;

create table emp_nonexec as
select *
from emp
where 1=3;

-- This below one is a simple method of inserting into two table based on certain criteria. The all clause is used to execute all the inserts.

insert all
when sal > 3000 then
into emp_exec
when sal <= 3000 then into emp_nonexec select * from emp; -- 14 rows inserted The first clause is used to execute the first part first and when the condition is not met then execute the next part. insert first when sal > 3000 then
into emp_exec
else
into emp_nonexec
select * from emp;

-- 14 rows inserted

create table emp_name as
select empno , ename
from emp
where 1=3;

create table emp_dept as
select empno , deptno
from emp
where 1=3;

insert all
into emp_name values (empno , ename)
into emp_dept values (empno , deptno)
select * from emp;

-- 28 rows inserted