Merge Statement in SQL Server

With the Merge statement we can merge data from a souce table into a target table.

Synatx

  1. MERGE INTO <Target Table > AS TRG  
  2. USING <Souce Table> As SRC  
  3. ON <Merge Conidtion>  
  4. WHEN MATCHED [AND Condition]  
  5. THEN <Action>  
  6. WHEN NOT MATCHED [BY TARGET ] [AND Condition]  
  7. THEN <Action>  
  8. WHEN NOT MATCHED BY SOURCE [AND CONIDTION]  
  9. 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
  1. --Created Stored Procedure With Old Way  
  2. create procedure InsUpStudent  
  3. (@Name varchar(20),@Marks int)  
  4. as  
  5. begin  
  6.    if exists(select * from Student where Name=@Name)  
  7.      begin  
  8.             update Student set Marks=@Marks where Name=@Name  
  9.      end  
  10.    else   
  11.     begin  
  12.         insert into Student(Name,Marks) values(@Name,@Marks)  
  13.     end  
  14. end  
  15.   
  16. --Test Some Sample data with above procedure.  
  17. exec InsUpStudent 'Rakesh',500 --Here record need to insert into Student table beacuse of Rakesh does not exists.  
  18. exec InsUpStudent 'Rakesh',600 --Here record need to update into Student table beacuse of Rakesh already exists.  
Using Merege Statement
  1. truncate table Student  
  2. drop procedure InsUpStudent  
  3.   
  4. --Create Stored Procedure With Merge Statement  
  5. create procedure InsUpStudent  
  6. (@Name varchar(20),@Marks int)  
  7. as  
  8. begin  
  9.     merge into Student as trg  
  10.     using (values(@Name,@Marks)) as src(Name,Marks)  
  11.     on trg.Name=Src.Name  
  12.     when matched and (trg.Name<> src.Name or trg.Marks <>src.Marks)  then update  
  13.     set trg.Name=src.Name, trg.Marks=src.Marks  
  14.     when not matched then insert (Name,Marks)   
  15.     values(src.Name,src.Marks);  
  16. end  
  17.   
  18. --Test Some Sample data with above procedure.  
  19. exec InsUpStudent 'Rakesh',500 --Here record need to insert into Student table beacuse of Rakesh does not exists.  
  20. 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.