How to Do Multi Insert In SQL

MULTI TABLE 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 MULTI TABLE 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