By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.TableA (TableAId INT IDENTITY NOT NULL PRIMARY KEY, CommonField1 INT, CommonField2 INT, GeneratedTableBId INT NULL, TableAPrivateField VARCHAR(50));
CREATE TABLE dbo.TableB (TableBId INT IDENTITY NOT NULL PRIMARY KEY, CommonField1 INT, CommonField2 INT);
INSERT dbo.TableA (CommonField1, CommonField2, TableAPrivateField)
VALUES (1, 2, 'MyCondition'), (2, 3, 'Not MyCondition'), (3, 4, 'MyCondition');
3 rows affected
DECLARE @InsertedTableB TABLE (TableBId INT PRIMARY KEY, TableAId INT NOT NULL);
MERGE INTO dbo.TableB AS b
USING
( SELECT TableAId, CommonField1, CommonField2
FROM dbo.TableA
WHERE TableAPrivateField = 'MyCondition'
) AS a
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (CommonField1, CommonField2)
VALUES (a.CommonField1, a.CommonField2)
OUTPUT inserted.TableBId, a.TableAId INTO @InsertedTableB (TableBId, TableAId);
UPDATE a
SET GeneratedTableBId = b.TableBId
FROM dbo.TableA AS a
INNER JOIN @InsertedTableB AS b
ON b.TableAId = a.TableAId;
4 rows affected
SELECT *
FROM dbo.TableA;
SELECT *
FROM dbo.TableB;
| TableAId | CommonField1 | CommonField2 | GeneratedTableBId | TableAPrivateField |
|---|---|---|---|---|
| 1 | 1 | 2 | 1 | MyCondition |
| 2 | 2 | 3 | null | Not MyCondition |
| 3 | 3 | 4 | 2 | MyCondition |
| TableBId | CommonField1 | CommonField2 |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 3 | 4 |