SQL Server 2019伪自引用表数据迁移：集合逻辑实现方案问询

可以通过**OUTPUT子句结合临时映射表**的集合式方案实现，完全避免逐行（RBAR）处理，具体步骤如下：

核心思路 #

利用SQL Server的INSERT ... OUTPUT特性捕获插入时自动生成的Identity主键，同时记录源表的原主键和伪自引用字段值，形成源-新ID的映射关系，最后通过这个映射批量更新目标表的伪自引用字段。

具体实现 #

假设：

• 源表：SourceTable，主键SourceID（数据类型与目标表PK不同，比如VARCHAR(50)），伪自引用字段SourceParentID
• 目标表：TargetTable，主键TargetID（INT IDENTITY），伪自引用字段TargetParentID

1. 创建临时映射表 #

用来存储源ID、新生成的目标ID，以及源表的自引用值：

CREATE TABLE #IDMapping (
    SourceID VARCHAR(50), -- 匹配源表PK数据类型
    TargetID INT,
    SourceParentID VARCHAR(50) -- 匹配源表自引用字段类型
);

2. 插入数据并捕获ID映射 #

插入源表数据到目标表，同时通过OUTPUT把映射关系写入临时表：

INSERT INTO TargetTable (Col1, Col2, TargetParentID, ...) -- 列出目标表除TargetID外的所有需要插入的列
OUTPUT inserted.TargetID, s.SourceID, s.SourceParentID INTO #IDMapping
SELECT 
    s.Col1, 
    s.Col2, 
    s.SourceParentID, -- 先临时写入源的自引用值，后续替换为新ID
    ... -- 其他源表对应列
FROM SourceTable s;

3. 批量更新伪自引用字段 #

基于临时映射表，把目标表中临时写入的源自引用值替换为对应的新生成Identity主键：

UPDATE t
SET t.TargetParentID = parent_m.TargetID
FROM TargetTable t
JOIN #IDMapping m ON t.TargetID = m.TargetID
JOIN #IDMapping parent_m ON m.SourceParentID = parent_m.SourceID;

4. 事务保障（可选但推荐） #

为了确保插入和更新操作的原子性，避免中途出错导致数据不一致，可以包裹在事务中：

BEGIN TRANSACTION;

-- 执行上述1-3步骤的代码

COMMIT TRANSACTION;

关键说明 #

• 临时表的字段类型必须严格匹配源表对应字段，避免类型转换错误
• 如果源表的伪自引用字段存在NULL值（如顶级节点），上述UPDATE会自动忽略这些行，保持TargetParentID为NULL，符合原逻辑
• 全程为集合式操作，性能远优于逐行循环，适合大数据量迁移
• 完全符合约束：无需修改目标表架构，不影响现有数据，迁移后伪自引用关系保持有效

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