How to Update, Insert, Delete in one MERGE query in Sql Server 2008?

merge, sql, sql-server-2008

Solution

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

Problem

I have two tables - the source and the destination. I would like to merge 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 there are more than 3, of course) - id, checksum and timestamp. - Each source record has two fields - id and checksum. - A source record is to be inserted into the destination if there is no destination record with the same id. - A destination record will be updated from the source record with the same id provided the source record checksum IS NOT NULL. It is guaranteed that if the checksum IS NOT NULL then it is different from the respective destination checksum. This is a given. - A destination record will be deleted if there is no source record with the same id. This setup should lend itself quite well to the MERGE statement semantics, yet I am unable to implement it. My poor attempt is documented in this SQL Fiddle What am I doing wrong? EDIT BTW, not MERGE based solution is here.

Original source