3

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.

iuliu.net
  • 6,666
  • 6
  • 46
  • 69

1 Answers1

4

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

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • On the 2nd to last line: `A MERGE statement must be terminated with a semicolon (;)` EDIT: Yeah, you forgot a colon on the 2nd to 2nd-to-last line. – iuliu.net May 20 '16 at 06:45
  • This is great. However, I don't seem to be able to adapt your example to my case, aka `INSERT` with `SELECT FROM` statement, mind helping me with this? – iuliu.net May 20 '16 at 06:51
  • Have you tried running it and checking the result? `MERGE` is used as a workaround for your problem. – Felix Pamittan May 20 '16 at 06:55
  • Ohh! I am dumb. I didn't realise that the `VALUES(..` actually use columns from `src`, which is `BulkProducts` so it's kind of a `SELECT` in that case. Thanks, checking it now. – iuliu.net May 20 '16 at 06:57
  • Link that needs no login http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge/ – AjV Jsy Feb 07 '19 at 13:36