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
  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
  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
  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.