Merge statement was introduced by Microsoft in SQL Server 2005. It allows user to merge one table to another combining different ways of operation, inserting, deleting and updating, on the targeting table based upon the condition specified in the merge statement.
When Matched: It means when the record in the source table and target table matched by key. In this case, you have option of, update target or delete target.
When Not Matched: It means the records in the source table do not exist in the target. The possible operation is to insert record to the target.
When Not Matched By Source: it means the records are in target table but not in the source table. In this case, you can choose to update or remove the record in the target table .
There are potentially 3 operations, insert, delete, update, taking place at the “same time”. If I have a trigger capturing those 3 operations, how many times will the trigger be fired? If the trigger is only called once, we will have to change our habit writing triggers because potentially the count of records in inserted pseudo table may be different from deleted table. I know a lot of programmers using existence of record(s) in the inserted table and deleted table to determine the operation. If trigger is fired once regardless merge conditions and operations, it would potentially break existing application. let’s do a test
use tempdb
go
---clean up
set nocount on
if object_id('table1') is not null drop table table1
if object_id('table2') is not null drop table table2
if object_id('table3') is not null drop table table3
go
create table table1(id int, description varchar(20))
create table table2(id int, description varchar(20))
go
insert into table1 values(1,'A'),(3,'C'),(5,'E'),(7, 'G'), (9,'I')
insert into table2 values(2,'B'),(4,'D'),(5,'F'), (7, 'H'), (10, 'J')
create table table3(table_name varchar(20), type varchar(10),id int, description varchar(20), logid int identity(1,1) primary key, op_identifier uniqueidentifier)
go
create trigger t_table1 on table1
for insert, delete, update
as
begin
declare @op_identifier uniqueidentifier = newid()
insert into table3(table_name , type, id , description, op_identifier)
select 'table1', 'inserted', id, description, @op_identifier
from inserted
insert into table3(table_name , type, id , description, op_identifier)
select 'table1', 'deleted', id, description, @op_identifier
from deleted
end
go
create trigger t_table2 on table2
for insert, delete, update
as
begin
declare @op_identifier uniqueidentifier = newid()
insert into table3(table_name , type, id , description, op_identifier)
select 'table2', 'inserted', id, description, @op_identifier
from inserted
insert into table3(table_name , type, id , description, op_identifier)
select 'table2', 'deleted', id, description, @op_identifier
from deleted
end
go
merge table1 as t
using table2 as s on t.id = s.id
when matched then update set t.description = s.description
when not matched then insert(id, description) values(s.id, s.description)
when not matched by source then delete;
select * from table3
if object_id('table1') is not null drop table table1
if object_id('table2') is not null drop table table2
if object_id('table3') is not null drop table table3
Results are
The trigger has been called 3 times. First, the merge statement enter the new records which do not exist in the target, J, B and D. Second, it updates G and E to H and F and the last, it removes the un-matching records, I, C, A from the target table. The conclusion is that the merge statement internally execute insert, delete and update to the base table.