MULTIBLE 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.
