我正在尝试构建一个映射表,以便将表中新行的in与从中复制它们的in关联起来。OUTPUT INTO子句似乎非常适合这样做,但它的行为似乎与文档不符。
我的代码:
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中被选择。但这与在线书籍中的文档相矛盾。关于输出子句的文章中有一个示例E,它描述了类似的用法:
OUTPUT INTO子句返回被更新的表(WorkOrder)和Product表中的值。在FROM子句中使用Product表来指定要更新的行。
有没有人用过这个功能?
(在此期间,我重写了我的代码,使用游标循环来完成这项工作,但这很丑陋,我仍然很好奇)
https://stackoverflow.com/questions/155321
复制相似问题