Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 31 March 2016

SQL Multiple Inserts



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)
                       SELECTFROM dept WHERE deptno = 10;

     -- This inserts 3 rows


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.
 
Share this article :

0 comments:

Post a Comment