Consider an SQL MERGE statement merging 4 rows using a derived table into Target like so:
MERGE Target AS T USING
(VALUES
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
) as S(Account_ID,Device_ID,Device_Name)
ON T.Account_ID = S.Account_ID
WHEN MATCHED THEN UPDATE SET
Account_ID = S.Account_ID,
Device_ID = S.Device_ID,
Device_Name = S.Device_Name
WHEN NOT MATCHED THEN INSERT
(Account_ID,Device_ID,Device_Name)
VALUES (S.Account_ID,S.Device_ID,S.Device_Name)
OUTPUT $action, inserted.*;
Does the output (of inserted rows) respect the same order that is specified in the derived table? For example, assume rows 2 and 4 were inserted into Target. Does the output result maintain the same order like so [2,4].
For the tests that I performed, it seems to do it, but I could not find any reliable documentation around it.
If it helps Target has an auto-incrementing primary key as well.
As many responses have pointed out, the MERGE operation does not guarantee any ordering for the upserted rows.
However, the approach outlined here worked for my use case by specifying an incremental index (SourceId) for the rows and then doing some in-memory processing (in my NodeJS code) to sort the OUTPUT response by the SourceId.
MERGE Target AS T USING
(VALUES
(1, ?, ?, ?),
(2, ?, ?, ?),
(3, ?, ?, ?),
(4, ?, ?, ?),
) as S(SourceId, Account_ID,Device_ID,Device_Name)
ON T.Account_ID = S.Account_ID
WHEN MATCHED THEN UPDATE SET
Account_ID = S.Account_ID,
Device_ID = S.Device_ID,
Device_Name = S.Device_Name
WHEN NOT MATCHED THEN INSERT
(Account_ID,Device_ID,Device_Name)
VALUES (S.Account_ID,S.Device_ID,S.Device_Name)
OUTPUT $action, S.SourceId, inserted.*;