iuliu.net iuliu.net - 6 months ago 14
SQL Question

Output Inserted.Id AND another field

I have the following query:

DECLARE @OutProduct TABLE
(
ProductID INT,
BulkProductId INT
)

INSERT INTO dbo.Products
( EanCode ,
ChangedDateTime ,
ChangedById ,
Deleted
)
OUTPUT INSERTED.ID, BulkProducts.Id INTO @OutProduct (ProductID, BulkProductId)
SELECT EanCode ,
GETDATE(),
GETDATE(),
0
FROM dbo.BulkProducts
WHERE ProductId is NULL


Assuming
Products.Id
&
BulkProducts.Id
are auto-incrementing identity columns:

What I'm trying to achieve:

@OutProduct
temp table contains tuples made up of the just-inserted
Products.Id
and the Id of the row in
BulkProducts
.

What I've stumbled upon:
BulkProducts.Id
cannot be used in the
OUTPUT INSERTED.ID, BulkProducts.Id INTO
statement, as it's not valid syntax.

How can I solve this?

EDIT: I'm using SQL Server 2012.

Answer

You might want to explore MERGE:

MERGE INTO dbo.Products
USING dbo.BulkProducts AS src
    ON 1 = 0 -- Never match
WHEN NOT MATCHED THEN
    INSERT(EanCode, ChangedDateTime, ChangedById, Deleted)
    VALUES(src.EanCode, GETDATE(), GETDATE(), 0)
OUTPUT
    inserted.Id,
    src.Id
INTO @OutProduct;

Reference:

Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE by Adam Machanic