TSQL Merge using table valued parameter and assign auto generated / updated id back to the parameter

Question

I've a table type named product

CREATE TYPE [dbo].[udttProduct] AS TABLE(
[ProductID] [bigint] NOT NULL,
[ProductDescription] [varchar](500) NOT NULL,
[ProductCode] [varchar](50) NOT NULL)

And the stored procedure

CREATE PROCEDURE [dbo].[uspCreateOrUpdateProduct]
    @ProductParam udttProduct READONLY,
AS
BEGIN
    SET NOCOUNT ON;
    MERGE Product AS [Target] 
    USING @ProductParam AS [Source]
    ON 
        [Target].ProductCode = [Source].ProductCode
    WHEN MATCHED THEN
    UPDATE 
    SET
          [Target].ProductDescription = [Source].ProductDescription
          -- i would like to assign the updated ID back to @ProductParam so i can insert to log
    WHEN NOT MATCHED BY TARGET THEN
    INSERT 
        (
              ProductDescription
            , ProductCode
        )
        VALUES
        (
              [Source].[ProductDescription]
            , [Source].[ProductCode]
        );
        -- i would like to assign the auto generated ID back to @ProductParam so i can insert to log

    -- after insert / update, insert to log
    INSERT INTO [dbo].[ProductLog]
    (
        ProductId, -- so i can insert id to here
        ProductDescription,
        ProductCode
    )
    SELECT
        ProductID,
        ProductDescription,
        ProductCode
    FROM
        @ProductParam


    SET NOCOUNT OFF;
END
GO

When performing merging, i would like to retrieve the updated / new id back to the @ProductParam, so that i can insert the record to log with ProductID.

I've other stored procedure is using merge and would like to do the same to get the id and insert into log, but those stored procedure involve large amount of data and the transaction is like 1 second for 10000+ records.

I get the id using temp table, but i'm just wondering if merge could do this. If this is not the good way please advise me. thanks.

PS: My productID key is auto generated during insert.

Possible duplicate of [T-SQL - Insert Data into Parent and Child Tables](http://stackoverflow.com/questions/38213008/t-sql-insert-data-into-parent-and-child-tables) — Zohar Peled, Mar 13 '17 at 11:17

GarethD · Accepted Answer · 2017-03-13T11:27:24.550

You can use the OUTPUT clause of the MERGE statement to insert directly to your log table, and not worry about modifying your table valued parameter (which you can't do anyway because it is readonly):

MERGE Product AS [Target] 
USING @ProductParam AS [Source]
ON [Target].ProductCode = [Source].ProductCode
WHEN MATCHED THEN
    UPDATE SET [Target].ProductDescription = [Source].ProductDescription
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductDescription, ProductCode)
    VALUES ([Source].[ProductDescription], [Source].[ProductCode])
OUTPUT inserted.ProductID, inserted.ProductDescription, inserted.ProductCode
    INTO dbo.ProductLog (ProductID, ProductDescription, ProductCode);

The issue you may have is that if ProductLog.ProductID has a foreign key reference to Product.ProductID (which it should) then this technique won't work, you would need to stage the results into a new table, then do the insert:

DECLARE @tmpProductLog dbo.udttProduct;

MERGE Product AS [Target] 
USING @ProductParam AS [Source]
ON [Target].ProductCode = [Source].ProductCode
WHEN MATCHED THEN
    UPDATE SET [Target].ProductDescription = [Source].ProductDescription
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductDescription, ProductCode)
    VALUES ([Source].[ProductDescription], [Source].[ProductCode])
OUTPUT inserted.ProductID, inserted.ProductDescription, inserted.ProductCode
    INTO tmpProductLog (ProductID, ProductDescription, ProductCode);


INSERT dbo.ProductLog (ProductID, ProductDescription, ProductCode)
SELECT  ProductID, ProductDescription, ProductCode
FROM    @tmpProductLog;

TSQL Merge using table valued parameter and assign auto generated / updated id back to the parameter

1 Answers1