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

a) CREATE STUDENT TABLE

     SQL> Create table student(no number(2),name varchar(2),marks number(3));

b) 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

c) 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

d) 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)

e) 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.

f) 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

g) 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.

h) 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

i) MULTI INSERT WITH MULTIBLE 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.