Merge Statement Part-2

With Merge statement  we can merge the data from Souce table into Target table.

Synatx:

MERGE INTO <Target Table > AS TRG

USING <Souce Table> As SRC

ON <Merge Conidtion>

WHEN MATCHED [AND Condition]

THEN <Action>

WHEN NOT MATCHED [BY TARGET ] [AND Condition]

THEN <Action>

WHEN NOT MATCHED BY SOURCE [AND CONIDTION]

THEN <Action.

MERGE INTO <Target Table>: This cluase define the  Target table for the  operation.

USING <Source Table>:This clause define the Source table for the opearation. In Source Table we can use Table ,CTE,Dervied Table,Some Other Database Table,OPENROWSET, XQUERY.

ON<Merge Condition>: This is just like as ON Clause like in Joins. This Statement defines the Both Souce table and Target table Matched or NotMatched.

WHEN MATCHED [AND Condition]  THEN <ACTION>: This clause defines the When Both Souce Table and Target Table Matched based on key .Here [AND Condition] is optional . Here We can perform two Actions Either Update or Delete on target table.

WHEN NOT MATCHED [BY TARGET ] [AND Condition] THEN <ACTION>: This clause defines the When Target Table Matched based on key .Here [AND Condition] is optional . Here We can perform Only one ..Actions i,e. Insert.

WHEN NOT MATCHED BY SOURCE [AND Condition] THEN <ACTION>: This clause defines the When Source Table Matched based on key .Here [AND Condition] is optional .  Here We can perform two Actions Either Update or Delete on target table.

Real Tme Scenario Using Merge Statement

--Create Student Table

if object_id('Student') is null

create table Student(id int identity(1,1) ,Name varchar(20), Marks int)

--Created Stored Procedure With Old Way

create procedure InsUpStudent

(@Name varchar(20),@Marks int)

as

begin

   if exists(select * from Student where Name=@Name)

                 begin

                                update Student set Marks=@Marks where Name=@Name

                 end

   else

                begin

                                insert into Student(Name,Marks) values(@Name,@Marks)

                end

end

--Test Some Sample data with above procedure.

exec InsUpStudent 'Rakesh',500 --Here record need to insert into Student table beacuse of Rakesh does not exists.

exec InsUpStudent 'Rakesh',600 --Here record need to update into Student table beacuse of Rakesh already exists.

Using Merege Statement:

truncate table Student

drop procedure InsUpStudent

--Create Stored Procedure With Merge Statement

create procedure InsUpStudent

(@Name varchar(20),@Marks int)

as

begin

                merge into Student as trg

                using (values(@Name,@Marks)) as src(Name,Marks)

                on trg.Name=Src.Name and (trg.Name<> src.Name or trg.Marks <>src.Marks)

                when matched then update

                set trg.Name=src.Name, trg.Marks=src.Marks

                when not matched then insert (Name,Marks)

                values(src.Name,src.Marks);

end

--Test Some Sample data with above procedure.

exec InsUpStudent 'Rakesh',500 --Here record need to insert into Student table beacuse of Rakesh does not exists.

exec InsUpStudent 'Rakesh',600 --Here record need to update into Student table beacuse of Rakesh already exists.

Note : WHEN MATCHED [AND Condition]  THEN <ACTION>, WHEN NOT MATCHED [BY TARGET ] [AND Condition] THEN <ACTION> AND WHEN NOT MATCHED BY SOURCE [AND Condition] THEN <ACTION> All these clauses not mandatory only any of the one is required.