Update table row with inserted identity ID

Question

I have the following query:

INSERT INTO dbo.Products 
        ( Code , 
          ProducerCode , 
          CustomerId , 
          EanCode , 
          ChangedDateTime , 
          ChangedById , 
          Deleted  
        ) 
SELECT  Code , 
        ProducerCode , 
        CustomerId , 
        EanCode , 
        GETDATE(), 
        GETDATE(),
        0 
FROM dbo.BulkProducts 
WHERE ProductId is NULL AND BulkProducts.BulkId = @BulkId

The Products table has an IDENTITY ID column, which is assigned automatically on insert. What I want to achieve is, after the insertion in Products, have the assigned ID of the product in the row from BulkProducts. I have read a bit about@@IDENTITY, IDENT_CURRENT and SCOPE_IDENTITY but I cannot seem to get it to work for my example. Any help appreciated. I am using SQL Server 2012.

Assuming the select returns more then one row, you will need to add an output clause to the insert statement to get the recently entered record ids. — Zohar Peled, May 19 '16 at 13:14
A clear example is provided in the MSDN reference. Take a look at this and see if it helps. https://msdn.microsoft.com/en-us/library/ms187342.aspx — , May 19 '16 at 13:37
Possible dupe of :http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row — TheGameiswar, May 19 '16 at 14:12

Krishnraj Rana · Answer 1 · 2016-05-19T14:10:58.967

0

You can achieve it using Output clause, like this -

DECLARE @OutProduct TABLE 
(
    ProductID INT,
    Code INT
)

INSERT INTO dbo.Products 
        ( Code , 
          ProducerCode , 
          CustomerId , 
          EanCode , 
          ChangedDateTime , 
          ChangedById , 
          Deleted  
        ) 
OUTPUT INSERTED.ID, INSERTED.Code INTO @OutProduct (ProductID, Code)
SELECT  Code , 
        ProducerCode , 
        CustomerId , 
        EanCode , 
        GETDATE(), 
        GETDATE(),
        0 
FROM dbo.BulkProducts 
WHERE ProductId is NULL AND BulkProducts.BulkId = @BulkId

So Now all your inserted data are in @OutProduct table. Now you can update your BulkProducts table using that temp table like below.

UPDATE bprod 
    SET productID = tmp.ProductID
FROM BulkProducts bprod
INNER JOIN @OutProduct tmp  
   ON bprod.Code = tmp.Code
WHERE bprod.ProductId is NULL AND bprod.BulkId = @BulkId

edited May 19 '16 at 14:10

answered May 19 '16 at 13:17

Krishnraj Rana

6,516
2
29
36

The `OUTPUT INSERTED.ID INTO @OutProduct (ProductID, BulkId)` will insert both the current just-inserted-ID and BulkId? – iuliu.net May 19 '16 at 13:26
Ok but as you can see the `BulkId` field isn't getting inserted, so I don't think is accessible from `INSERTED`.. I've tried `OUTPUT INSERTED.ID, BulkProducts.BulkId INTO @OutProduct (ProductID, BulkId)` but it doesn't seem to work. – iuliu.net May 19 '16 at 13:40
Ok, you changed it with `Code`, but I still need the `BulkId` :D – iuliu.net May 19 '16 at 13:41
@iuliu.net: I understand. See my update query again at the last. I have added `Where` clause. I think now no need to BulkID – Krishnraj Rana May 19 '16 at 13:44

score 0 · Answer 2 · answered May 20 '16 at 08:41

Apparently this cannot be solved without a MERGE. The final solution looks like this:

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;

And like this, my @OutProduct table contains tuples of BulkProductId and ProductId which I can then use to update my BulkProducts table.

Update table row with inserted identity ID

2 Answers2