SQL Merge WHEN NOT MATCHED BY SOURCE THEN on filtered source and targed tables

Question

I have a 2 tables with PARENT-CHILD relationship and their "snapshot" versions:

CREATE TABLE Parent(
  ParentId int
)

CREATE TABLE Child(
  ChildId int,
  ParentId int,
  ColA int,
)

CREATE TABLE ParentSnapshot(
  ParentSnapshotId int,
  ParentId int
)

CREATE TABLE ChildSnaphost(
  ChildSnapshotId,
  ParentSnapshotId
  ChildId int,
  ParentId int,
  ColA int,
)

At some point, Parent and Child tables are copied to ParentSnapshot and ChildSnaphost:

Now, for given list of ParentSnapshotIds, I need to synchronize ChildSnaphost with their original data (Child table).

How do I write merge statement that for given list ParentSnapshotIds merges ChildTable into ChildSnaphost table:

1. Insert new entries into ChildSnaphost if created in ChildTable
2. Removes new entries into ChildSnaphost if not found in ChildTable
3. Updates matched entries

I'm struggling to write the where condition for list of ParentSnapshotIds, resp ParentIds

Answer 1

You need to join the ParentSnapshot Parent and Child tables in the merge source to get the new list of Child rows to merge to ChildSnapshot.

You also need to filter the target ChildSnapshot by that list of ParentSnapshotIds. Do NOT do what the other answer has done: an unrestricted merge into ChildSnapshot, as then all other rows will be deleted, even of snapshots you weren't interested in modifying.

Do NOT place these conditions in the ON clause. The only thing that goes in the ON clause is the join condition, no filters should be placed here.

WITH source AS (
    SELECT 
        ps.ParentSnapshotId,
        c.ChildId,
        ps.ParentId,
        c.ColA
    FROM 
        ParentSnapshot ps
    INNER JOIN 
        Child c ON ps.ParentId = c.ParentId
    WHERE 
        ps.ParentSnapshotId IN (1, 500) --assuming you have list of ParentSnapshotId
),
target AS (
    SELECT 
        cs.*
    FROM 
        ChildSnapshot cs
    WHERE 
        cs.ParentSnapshotId IN (1, 500) --assuming you have list of ParentSnapshotId
)
MERGE INTO target AS t
USING source AS s
ON t.ParentSnapshotId = s.ParentSnapshotId AND t.ChildId = s.ChildId

WHEN MATCHED THEN 
    UPDATE SET
        ParentId = s.ParentId,
        ColA = s.ColA

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ParentSnapshotId, ChildId, ParentId, ColA)
    VALUES (s.ParentSnapshotId, s.ChildId, s.ParentId, s.ColA)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

db<>fiddle