我有以下查询:
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 另一个字段
发布评论