SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Parent(
  ParentId int
);

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

CREATE TABLE ParentSnapshot(
  ParentSnapshotId int,
  ParentId int
);

CREATE TABLE ChildSnapshot(
  ChildSnapshotId Int IDENTITY(1,1),
  ParentSnapshotId Int ,
  ChildId int,
  ParentId int,
  ColA int,
);

INSERT INTO Parent (ParentId)
OUTPUT inserted.*
VALUES (1), (2);

INSERT INTO Child (ChildId, ParentId, ColA)
OUTPUT inserted.*
VALUES
  (11, 1, 100),
  (22, 1, 200),
  (44, 1, 400);

INSERT INTO ParentSnapshot (ParentSnapshotId, ParentId)
OUTPUT inserted.*


ParentId


1

2




ChildId
ParentId
ColA


11
1
100

22
1
200

44
1
400




ParentSnapshotId
ParentId


1
1

2
2




ChildSnapshotId
ParentSnapshotId
ChildId
ParentId
ColA


1
1
11
1
100

2
1
33
1
300

3
1
44
1
450

4
2
10
2
300


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;

4 rows affected

select * from ChildSnapshot order by ChildId;


ChildSnapshotId
ParentSnapshotId
ChildId
ParentId
ColA


4
2
10
2
300

1
1
11
1
100

5
1
22
1
200

3
1
44
1
400