CODEWITHSUNDEEP
sqlsql-server-2008merge
mark
mark

Reputation: 62736

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

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:

  1. Each destination record has three fields (in a real application there are more than 3, of course) - id, checksum and timestamp.
  2. Each source record has two fields - id and checksum.
  3. A source record is to be inserted into the destination if there is no destination record with the same id.
  4. 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.
  5. 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.

Upvotes: 4

Views: 4972

Answers (1)

Yulia Dyachenko
Yulia Dyachenko

Reputation: 66

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

Upvotes: 5

Related Questions