TSQL使用表值参数合并，并将自动生成 /更新的ID分配回参数

Question

我有一个名为product的表类型

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

和存储过程

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

进行合并时，我想将更新/新ID检索回@productParam，以便我可以将记录插入与Productid登录。

我的其他存储过程正在使用合并，并且想做同样的事情以获取ID并插入日志中，但是这些存储的过程涉及大量数据，对于10000 记录，交易大约是1秒。<<<<<<<<<<<<<</p>

我使用TEMP表获得ID，但我只是想知道Merge是否可以这样做。如果这不是好方法，请告知我。谢谢。

ps：我的Productid密钥是在插入过程中自动生成的。

Answer 1

您可以使用MERGE语句的OUTPUT子句直接插入到日志表中，而不必担心修改表值参数（无论如何您都无法做到，因为它是ReadOnlyly的）：

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);

您可能遇到的问题是，如果ProductLog.ProductID对Product.ProductID有外键参考（应该这样），那么此技术将无法正常工作，您将需要将结果分为新表格，然后执行插件：

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;