如何在Azure Data Factory中实现跨Azure SQL DB插入后回写标识列？

一、类似IDENTITY_INSERT的显式插入实现 #

在ADF中无法直接像SSMS那样交互式执行SET IDENTITY_INSERT，但可以通过以下两种方式实现手动指定标识列ID2的值插入目标表：

1. 复制活动+预/后脚本 #

在复制活动的目标端设置中，添加预复制脚本开启IDENTITY_INSERT：

SET IDENTITY_INSERT [目标数据库名].[dbo].[目标表名] ON;

完成数据复制后，在后复制脚本中关闭：

SET IDENTITY_INSERT [目标数据库名].[dbo].[目标表名] OFF;

注意：需确保复制活动的字段映射中，源库的ID2（或手动指定的值）已映射到目标表的ID2字段，且目标表的ID2为标识列。

2. 存储过程活动 #

编写存储过程封装插入逻辑，在过程内部控制IDENTITY_INSERT的开关，示例存储过程：

CREATE PROCEDURE InsertTargetTable
    @ID1 INT,
    @Name NVARCHAR(50),
    @ID2 INT
AS
BEGIN
    SET IDENTITY_INSERT [目标数据库名].[dbo].[目标表名] ON;
    INSERT INTO [目标数据库名].[dbo].[目标表名] (ID2, Name)
    VALUES (@ID2, @Name);
    SET IDENTITY_INSERT [目标数据库名].[dbo].[目标表名] OFF;
END

然后在ADF中用存储过程活动调用该过程，传入对应参数即可。

二、自动生成ID2后的回写更新流程 #

如果目标库的ID2是自动生成的标识列，需要先捕获生成的ID2，再关联源库ID1回写更新，步骤如下：

1. 捕获生成的ID2 #

• 查找活动关联法：复制活动完成后，添加查找活动，通过唯一关联字段（如Name，若Name不唯一，建议在目标表临时添加ID1字段存储源库标识）查询目标表，获取ID2与源库ID1的对应关系，示例查询语句：

  SELECT t.ID2, s.ID1
  FROM [目标数据库名].[dbo].[目标表名] t
  JOIN [源数据库名].[dbo].[源表名] s ON t.Name = s.Name
  WHERE s.ID2 IS NULL -- 只处理未更新的记录
  

• 存储过程输出法：在插入存储过程中，用OUTPUT子句返回生成的ID2和对应的源库ID1，示例：

  CREATE PROCEDURE InsertAndReturnID
      @ID1 INT,
      @Name NVARCHAR(50)
  AS
  BEGIN
      INSERT INTO [目标数据库名].[dbo].[目标表名] (Name)
      OUTPUT inserted.ID2, @ID1
      VALUES (@Name);
  END
  

  ADF调用该存储过程后，可直接获取ID2与ID1的对应结果集。

2. 回写更新源库 #

拿到ID2和ID1的对应关系后，用脚本活动或存储过程活动执行更新，示例脚本：

UPDATE s
SET s.ID2 = t.ID2
FROM [源数据库名].[dbo].[源表名] s
JOIN (
    -- 此处替换为查找活动或存储过程返回的对应数据
    SELECT 1 AS ID1, 1 AS ID2 UNION ALL
    SELECT 2 AS ID1, 2 AS ID2
) t ON s.ID1 = t.ID1

若使用ADF动态参数，可将查找活动的结果集作为参数传入脚本，实现动态更新。

完整流程示例 #

1. （可选）预复制脚本开启目标表IDENTITY_INSERT（手动指定ID2时用）
2. 复制活动/存储过程活动：将源库数据插入目标库
3. （可选）后复制脚本关闭IDENTITY_INSERT
4. 查找活动/存储过程活动：获取ID2与源库ID1的对应关系
5. 脚本/存储过程活动：更新源库的ID2字段

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