The multi table insert
feature allows the INSERT . . . SELECT statement to use multiple tables as
targets. It also distributes data among target tables based on logical
attributes of the new rows.
Multi table insert
enables a single scan and transformation of source data to insert data into multiple
tables, sharply increasing performance
SQL>create table emp1 as select * from emp where 0=1;
SQL>create table emp2 as select * from emp where 0=1;
SQL>create table emp3 as select * from emp where 0=1;
SQL> insert first
when sal < 2000 then
into emp1
when sal > 3000 then
into emp2
else
into emp3
select * from emp
We have table called DEPT with the following columns and data
DEPTNO DNAME LOC
-------- -------- ----
10 accounting new york
20 research dallas
30 sales Chicago
40 operations boston
CREATE STUDENT TABLE |
SQL> CREATE TABLE student
(
no NUMBER (2),
name VARCHAR (2),
marks
NUMBER (3)
);MULTI INSERT WITH ALL FIELDS |
SQL> INSERT ALL
INTO
student VALUES (1, ’a’, 100)
INTO
student VALUES (2, ’b’, 200)
INTO
student VALUES (3, ’c’, 300)
SELECT * FROM dept WHERE deptno
= 10;
-- This inserts 3 rows
SQL>INSERT ALL
MULTI INSERT WITH SPECIFIED FIELDS |
SQL>INSERT ALL
INTO student (no, NAME) VALUES (4, 'd')
INTO student (NAME, marks) VALUES ('e', 400)
INTO student VALUES (3, 'c', 300)
SELECT * FROM dept WHERE deptno = 10;
-- This inserts 3 rows
MULTI INSERT WITH DUPLICATE ROWS |
SQL> INSERT ALL
INTO
student VALUES (1, ’a’, 100)
INTO
student VALUES (2, ’b’, 200)
INTO
student VALUES (3, ’c’, 300)
SELECT * FROM dept WHERE deptno
> 10;
-- This inserts 9 rows because in the select statement retrieves 3 records (3 inserts for each row retrieved)
MULTI INSERT WITH CONDITIONS BASED |
SQL> INSERT ALL
WHEN deptno > 10 THEN
INTO student1 VALUES (1, ’a’, 100)
WHEN dname = ‘SALES’ THEN
INTO student2 VALUES (2, ’b’, 200)
WHEN loc = ‘NEW YORK’ then
INTO student3 values(3,’c’,300)
SELECT * FROM dept WHERE deptno
> 10;
-- This inserts 4 rows because the first condition satisfied 3 times, second condition satisfied once and the last none.
MULTI INSERT WITH CONDITIONS BASED AND ELSE |
SQL> INSERT ALL
WHEN deptno
> 100 THEN
INTO
student1 VALUES (1, ’a’, 100)
WHEN dname = ‘S’ THEN
INTO
student2 VALUES (2, ’b’, 200)
WHEN loc = ‘NEW YORK’ then
INTO
student3 values(3,’c’,300)
ELSE
INTO
student values(4,’d’,400)
SELECT * FROM dept WHERE deptno
> 10;
-- This inserts 3 records because the else satisfied 3 times
MULTI INSERT WITH CONDITIONS BASED AND FIRST |
SQL> INSERT FIRST
WHEN deptno
= 20 THEN
INTO
student1 VALUES (1, ’a’, 100)
WHEN dname =
‘RESEARCH’ THEN
INTO
student2 VALUES (2, ’b’, 200)
WHEN loc = 'NEW YORK' THEN
INTO
student3 VALUES (3, ’c’, 300)
SELECT * FROM dept WHERE deptno
= 20;
-- This inserts 1 record because the first clause avoid to check the remaining conditions once the condition is satisfied.
MULTI INSERT WITH CONDITIONS BASED , FIRST AND ELSE |
SQL> INSERT FIRST
WHEN deptno
= 30 THEN
INTO
student1 VALUES (1, ’a’, 100)
WHEN dname = ‘R’ THEN
INTO
student2 VALUES (2, ’b’, 200)
WHEN loc = 'NEW YORK' THEN
INTO
student3 VALUES (3, ’c’, 300)
ELSE
INTO
student VALUES (4, ’d’, 400)
SELECT * FROM dept WHERE deptno
= 20;
-- This inserts 1 record because the else clause satisfied once
MULTI INSERT WITH MULTIPLE TABLES |
SQL> INSERT ALL
INTO
student1 VALUES (1, ’a’, 100)
INTO
student2 VALUES (2, ’b’, 200)
INTO
student3 VALUES (3, ’c’, 300)
SELECT * FROM dept WHERE deptno
= 10;
-- This inserts 3 rows
** You can use multi tables with specified fields, with duplicate rows, with conditions, with first and else clauses.
Multi
Table Insert Limitations
Multi table inserts can
only be performed on tables, not on views or materialized views.
Multi table insert via a
DB link cannot be performed Multi table inserts into nested tables cannot be performed
The sum of all the INTO
columns cannot exceed 999.
Sequences cannot be used
in the subquery of the multi table insert statement.
0 comments:
Post a Comment