Combine OUTPUT inserted.id with value from selected row

Question

Running SQL Server 2014. How can I insert multiple rows from a table and combine the inserted data with the new IDs?

Let's look at a stripped-down example!

DECLARE @Old TABLE 
(
    [ID] [int] PRIMARY KEY,
    [Data] [int] NOT NULL
)

DECLARE @New TABLE
(
    [ID] [int] PRIMARY KEY,
    [OtherID] [int] NULL
)

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID], [@Old].[ID] /* <--- not supported :( */ INTO @New
SELECT [Data]
FROM @Old

I need to combine the inserted IDs with the data that is being inserted. Can I assume that the inserted rows are in the same order as the selected rows? (I will not be able to join on [Data] after the insert operation.)

Update

The following seems like a possible solution, but I cannot find proof that it works. Is it guaranteed to work?

DECLARE @Old TABLE 
(
    [RowID] [int] PRIMARY KEY IDENTITY, -- Guaranteed insert order?     
    [ID] [int] NOT NULL,
    [Data] [int] NOT NULL
)

DECLARE @New TABLE
(
    [RowID] [int] PRIMARY KEY IDENTITY, -- Guaranteed insert order? 
    [ID] [int] NOT NULL,
    [OtherID] [int] NULL
)

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]

The trick here is to use a separate identity column and ORDER BY for the selected rows, and then joining on RowID.

When You INSERT, You'd use only inserted table. This means You have to add artificial field in @New table. — DimaSUN

Vladimir Baranov Vladimir Baranov · Accepted Answer · 2016-02-28T23:15:08

You can (ab)use MERGE with OUTPUT clause.

MERGE can INSERT, UPDATE and DELETE rows. In our case we need only to INSERT. 1=0 is always false, so the NOT MATCHED BY TARGET part is always executed. In general, there could be other branches, see docs. WHEN MATCHED is usually used to UPDATE; WHEN NOT MATCHED BY SOURCE is usually used to DELETE, but we don't need them here.

This convoluted form of MERGE is equivalent to simple INSERT, but unlike simple INSERT its OUTPUT clause allows to refer to the columns that we need. It allows to retrieve columns from both source and destination tables thus saving a mapping between old and new IDs.

MERGE INTO [dbo].[Test]
USING
(
    SELECT [Data]
    FROM @Old AS O
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Data])
VALUES (Src.[Data])
OUTPUT Src.ID AS OldID, inserted.ID AS NewID
INTO @New(ID, [OtherID])
;

Regarding your update and relying on the order of generated IDENTITY values.

In the simple case, when [dbo].[Test] has IDENTITY column, then INSERT with ORDER BY will guarantee that the generated IDENTITY values would be in the specified order. See point 4 in Ordering guarantees in SQL Server. Mind you, it doesn't guarantee the physical order of inserted rows, but it guarantees the order in which IDENTITY values are generated.

INSERT INTO [dbo].[Test] ([Data])
SELECT [Data]
FROM @Old
ORDER BY [RowID]

But, when you use the OUTPUT clause:

INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]

the rows in the OUTPUT stream are not ordered. At least, strictly speaking, ORDER BY in the query applies to the primary INSERT operation, but there is nothing there that says what is the order of the OUTPUT. So, I would not try to rely on that. Either use MERGE or add an extra column to store the mapping between IDs explicitly.

Combine OUTPUT inserted.id with value from selected row

Update

2 Answers