SQL : Learn how to Insert Update Delete together with Merge into Command

Syncing from source to destination table with only one command

Scenario & Use case

Syncing data from source table to destination table

Step 1: Data preparation

create table test_src (
 id int identity,
 product varchar(255),
 code varchar(255)
)

create table test_dest (
 id int identity,
 product varchar(255),
 code varchar(255)
)

insert into test_src (product, code) values ('foo', '111'), ('bar', '222')
insert into test_dest (product, code) select product, code from test_src

Now our source and destination is in sync

Step 2: Simulate the change

insert into test_src (product, code) values ('baz', '333')
delete from test_src where product = 'foo'
update test_src set code = '223' where product = 'bar' 

Step 3: Use MERGE INTO to keep the destination in sync

merge into test_dest as dest
using test_src as src
on dest.id = src.id
when matched then
update
set dest.product = src.product,
dest.code = src.code
when not matched then
insert (product, code)
values (src.product, src.code)
when not matched by source then delete;

You can check the result by

select * from test_dest

You can reset and try this again with the following command

truncate table test_dest
insert into test_src (product, code) values ('foo', '111'), ('bar', '222')
insert into test_dest (product, code) select product, code from test_src

select * from test_src
select * from test_dest

For more complex use case

You can see that after MATCHED keyword, it is the SQL statement

Then you can modify it into anything to fit your use case.

If we do not have MERGE INTO

We will have to do 3 things

1. Insert the differences

insert into test_dest (product, code)
select test_src.product, test_src.code from test_src
left join test_dest
on test_src.id = test_dest.id
where test_dest.id is null

2. Update the existing

-- Update data
update test_dest 
set test_dest.product = test_src.product,
test_dest.code = test_src.code
from test_dest
inner join test_src on test_dest.id = test_src.id

3. Remove deleted record

delete test_dest
from test_dest 
left join test_src on test_dest.id = test_src.id
where test_src.id is null

…which can be too many work