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

Pages

Thursday, 31 March 2016

SQL Multiple Inserts


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

0 comments:

Post a Comment