使用DELETE FROM...OUTPUT...INTO时获取目标表Identity值

这个问题我之前也碰到过，确实，直接用双OUTPUT的方式拿不到ArchiveTable的自增ID——因为DELETE语句的OUTPUT只能访问当前删除操作的DELETED集合，没法获取插入归档表时生成的新自增ID。下面给你一个可行的解决方案，用表变量中转数据，就能准确关联两边的信息：

具体步骤与代码 #

1. 先声明两个表变量：一个暂存要删除的源表记录，另一个存储归档表自增ID和源表ID的映射关系
2. 执行DELETE操作，把待归档的源表数据暂存到第一个表变量
3. 将暂存数据插入归档表，同时用OUTPUT捕获生成的自增ID与对应源表ID的映射
4. 最后关联两个表变量，返回包含源表所有字段和对应归档ID的结果

-- 声明表变量，暂存要删除的源表记录
DECLARE @DeletedRecords TABLE (
    ID INT,
    Code VARCHAR(16),
    Title NVARCHAR(128)
);

-- 声明表变量，存储归档ID与源表ID的映射
DECLARE @ArchiveMap TABLE (
    ArchiveID INT,
    OldSourceID INT
);

-- 第一步：删除源表数据，将删除的记录暂存到@DeletedRecords
DELETE FROM SourceTable
OUTPUT DELETED.ID, DELETED.Code, DELETED.Title INTO @DeletedRecords
WHERE Condition; -- 替换成你的实际筛选条件

-- 第二步：将暂存的数据插入归档表，同时捕获生成的自增ID
INSERT INTO ArchiveTable (OldID, Code, Title)
OUTPUT INSERTED.ID, INSERTED.OldID INTO @ArchiveMap(ArchiveID, OldSourceID)
SELECT ID, Code, Title FROM @DeletedRecords;

-- 第三步：关联两个表变量，返回最终结果（源表记录+对应归档ID）
SELECT 
    dr.*,
    am.ArchiveID AS ArchiveTableID
FROM @DeletedRecords dr
JOIN @ArchiveMap am ON dr.ID = am.OldSourceID;

为什么这个方法可行？ #

• 用表变量@DeletedRecords暂存删除的源表数据，保证后续插入归档表的数据和删除的完全一致
• 插入归档表时的OUTPUT可以捕获每一行生成的自增ID，同时关联对应的源表OldID，这样批量删除时也能准确对应每条记录的归档ID
• 最后通过关联两个表变量，就能得到你需要的完整结果集

如果是在高并发场景下，建议把这些操作放在一个事务里，避免中间出现数据不一致的情况。

内容的提问来源于stack exchange，提问作者Fred