SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



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 Int IDENTITY(1,1),
  ParentSnapshotId Int ,
  ChildId int,
  ParentId int,
  ColA int,
);
INSERT INTO Parent (ParentId) VALUES (1);


1 rows affected

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

3 rows affected

select * from Child;


ChildId
ParentId
ColA


11
1
100

22
1
200

44
1
400


INSERT INTO ParentSnapshot (ParentSnapshotId, ParentId) VALUES (1, 1);


1 rows affected

INSERT INTO ChildSnaphost ( ParentSnapshotId, ChildId, ParentId, ColA)
  VALUES ( 1, 11, 1, 100);
INSERT INTO ChildSnaphost ( ParentSnapshotId, ChildId, ParentId, ColA)
  VALUES ( 1, 33, 1, 300);
INSERT INTO ChildSnaphost ( ParentSnapshotId, ChildId, ParentId, ColA)
  VALUES ( 1, 44, 1, 450);

3 rows affected

select * from ChildSnaphost ;


ChildSnapshotId
ParentSnapshotId
ChildId
ParentId
ColA


1
1
11
1
100

2
1
33
1
300

3
1
44
1
450


MERGE INTO ChildSnaphost AS target
USING (
    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
) AS source
ON (target.ParentSnapshotId = source.ParentSnapshotId AND target.ChildId = source.ChildId)


WHEN MATCHED THEN 
    UPDATE SET
        target.ParentId = source.ParentId,
        target.ColA = source.ColA

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

WHEN NOT MATCHED BY SOURCE  THEN
    DELETE;

4 rows affected

select * from ChildSnaphost order by ChildId;


ChildSnapshotId
ParentSnapshotId
ChildId
ParentId
ColA


1
1
11
1
100

4
1
22
1
200

3
1
44
1
400