I currently have two tables like the following:

Table A

Table B

What i want to do is insert into TableB some record fetched from TableA, and then update the column [TableBGenerateId] of TableA with the corresponding new Id from the inserted record in TableB.

I tried with declaring a Table Value Parameter and then use it with the OUTPUT clause, but i can't find a way to relate back to the original TableAId of the row that acted as the source for the insert

something like that:

DECLARE @InsertedTableB TABLE (
                               TableBId INT PRIMARY KEY
                              );

INSERT INTO TableB
OUTPUT inserted.TableBId INTO @InsertedTableB
SELECT CommonField1, CommonField2,..
FROM TableA
WHERE TableAPrivateField = 'MyCondition';

WITH NumberedTableA AS(
                       SELECT TableAId, ROW_NUMBER() OVER(ORDER BY TableAId) AS RowNum
                       FROM TableA
                       WHERE TableAPrivateField = 'MyCondition'

                       ),
     NumberedInsert AS(
                       SELECT TableBId, ROW_NUMBER() OVER(ORDER BY TableBId) AS RowNum
                       FROM @InsertedTableB
                       )

UPDATE TableA
SET GeneratedTableBId = NumberedInsert.TableBId
FROM TableA
JOIN NumberedTableA ON Table.TableAId = NumberedTableA.TableAId
JOIN NumberedInsert ON NumberedTableA.RowNum = NumberedTable.RowNum

My problem is that even thought the query works i have no guaranties that the order of the fetched records will be the same, so i would risk linking back the wrong Ids. I tried to figure out some different solutions, but the closest one i found was to temporarily add a column to TableB containing TableAId and then perform the update, but i disliked it because this operation needs to be executed frequently and it would be too performance demanding. Adding the column permanently also isn't an acceptable solution sadly.

Anyone has any suggestion on how solve this?

CodePudding user response：

If you use MERGE rather than INSERT (but still only ever insert with the MERGE), you can capture both the ID from TableA, and the new ID from tableB in the OUTPUT clause and insert this to your table variable. Then use this table variable to update tableA:

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;

Example on db<>fiddle

Whenever I post any answer that in anyway condones the use of MERGE it is met with at least one comment highlighting all of the bugs with it, so to pre-empt that: There are a lot of issues with using MERGE in SQL Server - I do not believe that any of those risks will apply in this scenario if you are (a) forcing an insert and (b) using a table as the target. So while I will always avoid MERGE where I can by using multiple statements, this is one scenario where I don't avoid it because I don't think there is a cleaner solution available without using MERGE. It is anecdotal, but I have used this method for years and have never once encountered an issue.