Multitable Inserts

Multitable Inserts:

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions.

Scripts:

Scneraio1:
drop table org_tab;
drop table dept_tab;
drop table mgr_tab;

create table org_tab ( empid number,dept_name varchar2(10),manager_name varchar2(10));
insert into org_tab values ( 101,'sales','A');
insert into org_tab values ( 102,'sales','A');
insert into org_tab values ( 103,'sales','A');
insert into org_tab values ( 104,'sales','A');
insert into org_tab values ( 105,'sales','A');
insert into org_tab values ( 106,'Marketing','B');
insert into org_tab values ( 107,'Marketing','B');
insert into org_tab values ( 108,'Marketing','B');
insert into org_tab values ( 109,'Marketing','B');
insert into org_tab values ( 110,'Marketing','B');

SQL> select * from org_tab;
EMPID DEPT_NAME MANAGER_NA
---------- ---------- ----------
101 sales A
102 sales A
103 sales A
104 sales A
105 sales A
106 Marketing B
107 Marketing B
108 Marketing B
109 Marketing B
110 Marketing B

10 rows selected.

create table dept_tab (empid number,dept_name varchar2(10));
create table mgr_tab (empid number,manager_name varchar2(10));

SQL> INSERT ALL
2 INTO dept_tab values ( empid,dept_name)
3 INTO mgr_tab values ( empid,manager_name)
4 SELECT empid,dept_name,manager_name from org_tab ;

20 rows created.

SQL> select * from dept_tab;
EMPID DEPT_NAME
---------- ----------
101 sales
102 sales
103 sales
104 sales
105 sales
106 Marketing
107 Marketing
108 Marketing
109 Marketing
110 Marketing

10 rows selected.
SQL> select * from mgr_tab;
EMPID MANAGER_NA
---------- ----------
101 A
102 A
103 A
104 A
105 A
106 B
107 B
108 B
109 B
110 B

10 rows selected.
Scneraio2:

drop table dept_sales;
drop table dept_market;
create table dept_sales (empid number,dept_name varchar2(10));
create table dept_market (empid number,dept_name varchar2(10));


SQL> INSERT ALL
2 WHEN dept_name = 'sales' THEN
3 INTO dept_sales values ( empid,dept_name)
4 WHEN dept_name = 'Marketing' THEN
5 INTO dept_market values ( empid,dept_name)
6 SELECT empid,dept_name,manager_name from org_tab ;

10 rows created.

SQL> select * from dept_sales;
EMPID DEPT_NAME
---------- ----------
101 sales
102 sales
103 sales
104 sales
105 sales

SQL> select * from dept_market;
EMPID DEPT_NAME
---------- ----------
106 Marketing
107 Marketing
108 Marketing
109 Marketing
110 Marketing
The restrictions on multitable INSERTs are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.