咨询Azure SQL Database多表多列批量更新的高效实现方案

一、SQL原生批量处理方案 #

1. 透视源数据（整合同表同Id的多列数据） #

假设你的源表名为UpdateSource，先通过聚合将同一Table Name和Id的多列值打包为JSON，便于后续关联更新：

WITH PivotedUpdates AS (
    SELECT 
        [Table Name],
        Id,
        JSON_OBJECT_AGG([Column Name], Value) AS ColumnValues
    FROM UpdateSource
    GROUP BY [Table Name], Id
)

2. 分表批量更新 #

针对每个目标表，通过JOIN关联透视后的数据，提取JSON中的值完成批量更新：

Table 1的批量更新：

UPDATE t
SET 
    column1 = JSON_VALUE(p.ColumnValues, '$.column1'),
    column2 = TRY_CAST(JSON_VALUE(p.ColumnValues, '$.column2') AS DATE)
FROM [Table 1] t
JOIN PivotedUpdates p ON t.Id = p.Id AND p.[Table Name] = 'Table 1'
WHERE JSON_VALUE(p.ColumnValues, '$.column1') IS NOT NULL 
   OR JSON_VALUE(p.ColumnValues, '$.column2') IS NOT NULL;

Table 2的批量更新：

UPDATE t
SET 
    column6 = JSON_VALUE(p.ColumnValues, '$.column6'),
    column12 = TRY_CAST(JSON_VALUE(p.ColumnValues, '$.column12') AS DATE)
FROM [Table 2] t
JOIN PivotedUpdates p ON t.Id = p.Id AND p.[Table Name] = 'Table 2'
WHERE JSON_VALUE(p.ColumnValues, '$.column6') IS NOT NULL 
   OR JSON_VALUE(p.ColumnValues, '$.column12') IS NOT NULL;

3. 动态SQL（适配动态变化的表和列） #

如果源表中的目标表、列不固定，可自动生成所有更新语句：

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += N'
UPDATE t
SET ' + STRING_AGG(
    QUOTENAME([Column Name]) + ' = TRY_CAST(JSON_VALUE(p.ColumnValues, ''$.' + [Column Name] + ''') AS ' + 
    (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = us.[Table Name] AND COLUMN_NAME = us.[Column Name]) + ')',
    ','
) + N'
FROM ' + QUOTENAME([Table Name]) + N' t
JOIN (
    SELECT Id, JSON_OBJECT_AGG([Column Name], Value) AS ColumnValues
    FROM UpdateSource
    WHERE [Table Name] = ''' + [Table Name] + '''
    GROUP BY Id
) p ON t.Id = p.Id;
'
FROM (SELECT DISTINCT [Table Name], [Column Name] FROM UpdateSource) us;

EXEC sp_executesql @SQL;

注意：需确保目标表列的数据类型与源表Value字段兼容，避免转换错误。

二、Azure Data Factory (ADF) 方案 #

1. 核心流程 #

• Lookup活动：从源表拉取所有待更新数据。
• ForEach活动：按Table Name分组循环，处理每个目标表的更新任务。
• Stored Procedure活动：调用目标库存储过程，传入聚合后的列值完成批量更新。

2. 具体操作 #

（1）Lookup活动配置

查询语句配置为：

SELECT [Table Name], Id, [Column Name], Value FROM UpdateSource

取消勾选"First row only"，获取全量数据。

（2）ForEach循环分组

设置Items为@distinct(activity('LookupUpdateSource').output.value, 'TableName')，按表名去重循环。

（3）存储过程批量更新

先在目标数据库创建存储过程：

CREATE PROCEDURE dbo.BatchUpdateTable
    @TableName NVARCHAR(128),
    @Id INT,
    @ColumnValues NVARCHAR(MAX)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
    UPDATE ' + QUOTENAME(@TableName) + N'
    SET ' + 
    (SELECT STRING_AGG(QUOTENAME(j.[key]) + ' = TRY_CAST(''' + j.[value] + ''' AS ' + c.DATA_TYPE + ')', ',')
     FROM OPENJSON(@ColumnValues) j
     JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_NAME = @TableName AND c.COLUMN_NAME = j.[key]) + N'
    WHERE Id = @Id;';

    EXEC sp_executesql @SQL, N'@Id INT', @Id;
END

在ADF的Stored Procedure活动中传入TableName、Id和聚合后的ColumnValues参数即可。

性能优化要点 #

• 确保目标表的Id列存在索引，加速JOIN关联。
• SQL方案中尽量批量执行，避免单条更新；ADF方案可将同一表的多个Id打包传入存储过程，减少调用次数。

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