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.)
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
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With