Merge, delete when not on Source

Question

I have an issue with a merge statement into a FACT TABLE

It was pretty simple until the users started to delete records from the source.

Current SQL:

Set Count = 1
WHEN NOT MATCHED
   INSERT
WHEN MATCHED
 UPDATED

New SQL:

So in this example, a record has been deleted from the source, it no longer matches but there is nothing to insert. I would like it the count to be set to 0.

WHEN DELETED FROM SOURCE
Set Count = 0

.

Source
Bob Jones |  1111 
Mary Jones |  1112 
James Jones |  1113 
Helen Jones |  1114

TARGET
Bob Jones |  1111 | Count 1
Mary Jones |  1112| Count 1
James Jones |  1113| Count 1
Helen Jones | | 1114| Count 1
Peter Market |  1115| Count 0

I’m loading to a fact table using a merge and now they are just blanket deleting records, my facts are off. This must be accounted for somehow?

Thank you for any help at all.

Kaushik Nayak · Accepted Answer · 2018-10-10T17:25:40.070

You could do a merge using the target full outer joined with source, but id has to a unique key across both for this to work.

MERGE INTO target tgt 
USING (SELECT CASE 
                WHEN s.id IS NULL THEN t.name --write this logic for all other non id columns.
                ELSE s.name 
              END     AS name, 
              coalesce(s.id, t.id) AS id, --use target's id when no source id is available
              CASE 
                WHEN s.id IS NULL THEN 0 --zero for non matching ids
                ELSE 1 
              END      AS source_count 
       FROM   target t 
              full outer join source s 
                           ON s.id = t.id) src 
ON ( src.id=tgt.id) 
WHEN matched THEN 
  UPDATE SET tgt.name = src.name, 
             tgt.source_count = src.source_count 
WHEN NOT matched THEN 
  INSERT (id, 
          name, 
          source_count) 
  VALUES(src.id, 
         src.name, 
         1) ; --insert 1 by default for new rows

Demo

Merge, delete when not on Source

1 Answers1