Merge Statement in SQL Server

With the Merge statement we can merge data from a souce table into a 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 defines the target table for the operation.

USING <Source Table>: This clause defines the source table for the opearation. In Source Table we can use a table, CTE, Dervied Table, some other database table, OPENROWSET or XQUERY.

ON<Merge Condition>: This is just like an ON clause like in joins. This statement defines both the souce table and the target table as Matched or NotMatched.

WHEN MATCHED [AND Condition] THEN <ACTION>: This clause defines the When of both the Souce Table and the Target Table matched based on the key. Here [AND Condition] is optional. Here we can perform two actions, either update or delete on the target table.

WHEN NOT MATCHED [BY TARGET ] [AND Condition] THEN <ACTION>: This clause defines the When Target Table that is matched based on key. Here the [AND Condition] is optional. Here we can perform only the one action, Insert.

WHEN NOT MATCHED BY SOURCE [AND Condition] THEN <ACTION>: This clause defines the When Source Table that is matched based on key. Here the [AND Condition] is optional. Here we can perform one of two actions, either Update or Delete on the target table.

Realistic Scenario Using Merge Statement

--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
    when matched and (trg.Name<> src.Name or trg.Marks <>src.Marks)  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 are not mandatory, only one is required.