Merge Statement

by MM Gehlot · Published July 28, 2019 · Updated April 3, 2021

MERGE statement generally used in warehouse ETL process where we need to perform multiple operations like insert, update and delete data in target table based source table.

MERGE statement joins target to source by using ON condition.

For example I have 2 tables 1. vtype which one is main table, 2. load_vtype: this one use in ETL process, now its have some data like

Now I want to integrate so

if ids are same but name are deferent in vtype table then name will update to load_vtype
if ids not exists in vtype table but exists in load_vtype then insert
if ids exists in vtype table but not in load_vtype then delete

Merge Statement example:

merge vtype as target
using load_vtype as source on target.id=source.id
when matched and target.name <> source.name then
   update set target.name=source.name
when not matched by target then
   insert (id,name ) values (source.id,source.name)
when not matched by source then
   delete
OUTPUT $action, DELETED.id,DELETED.name,INSERTED.id,INSERTED.name;
— INTO @Logdata;

Their OUTPUT $action is optional for review or logdata save process can use, just seen in out put their 18 new row inserted into vtype(target) table which are not match to target, 1 row where id was same but name not matched so updated and 2 row delete from target table those are not exists in source table

Note: keep in mind if you have identity column in target column or references use as a foreign key in other table then maybe it will return same error like that:

Msg 544, Level 16, State 1, Line 91
Cannot insert explicit value for identity column in table ‘vtype1’ when IDENTITY_INSERT is set to OFF.

Variable also can use as source like :

Ex.1:

declare @ID int =1 ,@name varchar(100)=’abc’
merge vtype as target
using (values( @ID ,@name)) as source(id,name) on target.id=source.id
when matched and target.name <> source.name then
update set target.name=source.name
when not matched by target then
insert (id,name ) values (source.id,source.name)
OUTPUT $action, DELETED.id,DELETED.name,INSERTED.id,INSERTED.name;

Ex.2:

Note: we also can use view, subquery, function, temp table, table variable inside of using statement as source table.

Merge Statement