我正在尝试构建一个映射表,将表中新行的 ID 与从中复制的行关联起来。 OUTPUT INTO 子句似乎对此很完美,但它的行为似乎并不符合文档。
My code:
DECLARE @Missing TABLE (SrcContentID INT PRIMARY KEY )
INSERT INTO @Missing
( SrcContentID )
SELECT cshadow.ContentID
FROM Private.Content AS cshadow
LEFT JOIN Private.Content AS cglobal ON cshadow.Tag = cglobal.Tag
WHERE cglobal.ContentID IS NULL
PRINT 'Adding new content headers'
DECLARE @Inserted TABLE (SrcContentID INT PRIMARY KEY, TgtContentID INT )
INSERT INTO Private.Content
( Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, OrgUnitID )
OUTPUT cglobal.ContentID, INSERTED.ContentID INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, NULL
FROM Private.Content AS cglobal
INNER JOIN @Missing AS m ON cglobal.ContentID = m.SrcContentID
结果出现错误消息:
Msg 207, Level 16, State 1, Line 34
Invalid column name 'SrcContentID'.
(第 34 行是带有 OUTPUT INTO 的行)
实验表明,只有实际存在于 INSERT 目标中的行才能在 OUTPUT INTO 中选择。但这与在线书籍中的文档相矛盾。文章关于输出子句有示例 E 描述了类似的用法:
OUTPUT INTO 子句返回值
从正在更新的表中
(工单)也来自产品
桌子。产品表用于
FROM 子句指定行
更新。
有人使用过这个功能吗?
(与此同时,我重写了我的代码以使用光标循环来完成这项工作,但这很丑陋,我仍然很好奇)