输出 Inserted.Id AND 另一个字段 [英] Output Inserted.Id AND another field

问题描述

我有以下查询:

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

假设 Products.Id &BulkProducts.Id 是自动递增的标识列:

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

我正在努力实现的目标:

What I'm trying to achieve:

@OutProduct 临时表包含由刚刚插入的 Products.Id 和 BulkProducts 中行的 ID 组成的元组.

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

我偶然发现:BulkProducts.Id 不能在 OUTPUT INSERTED.ID, BulkProducts.Id INTO 语句中使用，因为它不是有效的语法.

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?

我使用的是 SQL Server 2012.

I'm using SQL Server 2012.

推荐答案

您可能想要探索 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;

参考:

博士.输出或:我如何学会停止担心并热爱亚当·马卡尼克的合并

这篇关于输出 Inserted.Id AND 另一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！