How to update, insert, delete in one MERGE query in Sql Server 2008?

Question

I have two tables - source and destination. I would like to combine the source into the destination using the MERGE query (SQL Server 2008).

My setup is as follows:

• Each destination record has three fields (in a real application, of course, more than 3) - identifier, checksum and timestamp.
• Each source record has two fields - an identifier and a checksum.
• The source record must be inserted at the destination if there is no destination record with the same identifier.
• The target record will be updated from the original record with the same identifier if the checksum of the original record is NOT. It is guaranteed that if the checksum is not NULL, then it is different from the checksum of the recipient. This is a given.
• The destination record will be deleted if there is no source record with the same identifier.

This setting is well suited to the semantics of the MERGE statement, but I cannot implement it.

My failed attempt is documented in SQL Fiddle.

What am I doing wrong?

EDIT

BTW, not a MERGE based solution here .

Answer 1

create table #Destination (id int,[Checksum] int,[Timestamp] datetime)
create table #Source (id int,[Checksum] int)

insert #Destination values(1,1,'1/1/2001'),(2,2,'2/2/2002'),(3,3,getdate()),(4,4,'4/4/2044')
insert #Source values(1,11),(2,NULL),(4,44);

merge #destination as D
using #Source as S
on (D.id=S.id)
when not matched by Target then 
Insert(id,[Checksum],[Timestamp])
Values(s.id,s.[Checksum],Getdate())
when matched and S.[Checksum] is not null then
Update set D.[Checksum]=S.[Checksum], D.[Timestamp]=Getdate()
when not matched by Source then
Delete
Output $action, inserted.*,deleted.*;

select * from #Destination