SQL Merge and Multi Table Insert examples

USING MERGE

MERGE

You can use merge command to perform insert and update in a single command.

Ex:

SQL> Merge into student1 s1

        Using (select *From student2) s2

        On(s1.no=s2.no)

        When matched then

        Update set marks = s2.marks

        When not matched then

        Insert (s1.no,s1.name,s1.marks)

        Values(s2.no,s2.name,s2.marks);

In the above the two tables are with the same structure but we can merge different structured    

tables also but the datatype of the columns should match.

Assume that student1 has columns like no,name,marks and student2 has columns like no,       

name, hno, city.

SQL> Merge into student1 s1

        Using (select *From student2) s2

        On(s1.no=s2.no)

        When matched then

        Update set marks = s2.hno

        When not matched then

        Insert (s1.no,s1.name,s1.marks)

        Values(s2.no,s2.name,s2.hno);

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.