Return inserted rows in specified order for Sql Server

Question

I need to return the rows from a table-valued insert in the same order they were specified.

I can use OUTPUT INTO

DECLARE @generated1 TABLE ([Id] varbinary(8), [OwnerId] [int]);

INSERT INTO [Blog] ([OwnerId])
OUTPUT INSERTED.*
INTO @generated1
VALUES ('1'),
('2'),
('1'),
('2'),
('2'),
('3'),
('3'),
('3'),
('3');

SELECT * FROM @generated1;

This usually works and returns

Id                  OwnerId
===========================
0x418B6EC7C6AC864D  1
0x6D0B89E56AB3EC48  2
0xE1B86C6A3C64AB42  1
0x51B8D9D1FCDE1647  2
0xB5AD578020CBCE4C  2
0x56CD3FF610080841  3
0x1D0D5B370A732C43  3
0x0B71CDB5CE6E0445  3
0x6A8AE3A2BD19924E  3

But if there is an FK defined on OwnerId and more than 125 rows are inserted the order in which they are inserted is different from the specified order.

Answer 1

One way this could be accomplished is by adding a sequential value to each row to be inserted, joining the generated table with the values that were specified and ordering by the added sequential value:

DECLARE @inserted1 TABLE ([Order] [int], [OwnerId] [int]);
INSERT INTO @inserted1
VALUES ('1', '1'),
('2', '2'),
('3', '1'),
('4', '3'),
('5', '2'),
('6', '3'),
('7', '3'),
('8', '2'),
('9', '3');

DECLARE @generated1 TABLE ([Id] varbinary(8), [OwnerId] [int]);
INSERT INTO [Blog] ([OwnerId])
OUTPUT INSERTED.[Id], INSERTED.[OwnerId]
INTO @generated1
SELECT [OwnerId] FROM @inserted1;

SELECT *
FROM (SELECT [g].[Id], [g].[OwnerId], [i].[Order]
FROM @generated1 [g]
INNER JOIN @inserted1 [i]
ON [g].[OwnerId] = [i].[OwnerId]) t
ORDER BY [Order];

But since OwnerId is non-unique this will produce more rows than inserted:

Id                  OwnerId Order
0x2557DCF354F9CD4E  1       1
0x3A265F70A2018249  1       1
0xA21503CD2F928144  2       2
0xE8C593480FCEAF41  2       2
0xC3E3C969BEA87641  2       2
0x2557DCF354F9CD4E  1       3
0x3A265F70A2018249  1       3
0x3F7EBD8EE702B44B  3       4
0xA3F09A3A612ACF41  3       4
0xA45D8F6FF779A74C  3       4
0x7BA9521290232D43  3       4
0xA21503CD2F928144  2       5
0xE8C593480FCEAF41  2       5
0xC3E3C969BEA87641  2       5
0x3F7EBD8EE702B44B  3       6
0xA3F09A3A612ACF41  3       6
0xA45D8F6FF779A74C  3       6
0x7BA9521290232D43  3       6
0x3F7EBD8EE702B44B  3       7
0xA3F09A3A612ACF41  3       7
0xA45D8F6FF779A74C  3       7
0x7BA9521290232D43  3       7
0xA21503CD2F928144  2       8
0xE8C593480FCEAF41  2       8
0xC3E3C969BEA87641  2       8
0x3F7EBD8EE702B44B  3       9
0xA3F09A3A612ACF41  3       9
0xA45D8F6FF779A74C  3       9
0x7BA9521290232D43  3       9

There are still only 9 unique values in Id and Order columns, the way they are combined shouldn't matter however, since the only value that identifies the row is OwnerId. The trick is to remove the rows from the result in a way that only 9 rows are returned with values in Id and Order being unique. Partitioning on both of the columns provides a way to deterministically order the combinations:

SELECT *
FROM (SELECT [g].[Id], [g].[OwnerId], [i].[Order],
ROW_NUMBER() OVER (PARTITION BY [g].[Id] ORDER BY [i].[Order]) AS RowNumber,
ROW_NUMBER() OVER (PARTITION BY [i].[Order] ORDER BY [g].[Id]) AS RowNumber2
FROM @generated1 [g]
INNER JOIN @inserted1 [i]
ON [g].[OwnerId] = [i].[OwnerId]) t
WHERE RowNumber = RowNumber2
ORDER BY [Order];

This returns the rows in the expected order:

Id                  OwnerId Order   RowNumber   RowNumber2
======================================================
0x2A51E4E35D2FA040  1       1       1           1
0x787E303904EC764C  2       2       1           1
0x778CE142E9760248  1       3       2           2
0xC056C57F1729E643  3       4       1           1
0xC0706FF6A8890E40  2       5       2           2
0x0E2058F3F142DF42  3       6       2           2
0x4690B24BE196374B  3       7       3           3
0x9F70CA6011ECD449  2       8       3           3
0xF35D87D1BDB2C34F  3       9       4           4