1/28/16

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.




0 comments:

Post a Comment