SQL-MULTIPLE INSERTS

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.