如何用基于集合的递归CTE替代循环生成单条MERGE语句？

现有一组通过查找表配合MERGE语句更新的表：

• ChannelInfo：以ChannelID为依据的查找表
• ChannelReadingMain：通过ChannelID定位筛选数据的表
• DeviceChannel：通过ChannelID查找目标表的表

当前做法是循环生成并执行多条MERGE语句，例如针对ChannelID=15的语句如下：

MERGE [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] T
USING
(
SELECT
[Si] as SIReading,
[Raw] as RawReading,
[TimeStamp] as ReadingDateTime
FROM
[#ChannelReadingMain]
WHERE
ChannelID = '15'
) S on T.ReadingDateTime = S.ReadingDateTime
WHEN NOT MATCHED BY TARGET
THEN
INSERT (ReadingDateTime,SIReading, RawReading)
VALUES (S.ReadingDateTime,S.SIReading, S.RawReading);

这种循环执行的方式耗时较长，尝试改用CTE写成单条基于集合的更新语句时，遇到错误：

Msg 40515, Level 15, State 1, Line 130
Reference to database and/or server name in 'b.cte2.ChannelReadingTablename' is not supported in this version of SQL Server.

推测错误和动态SQL中CTE的作用域有关，需要解决如何用CTE生成单条更新语句的问题。

首先明确：SQL Server无法直接在静态语句中通过CTE引用动态表名，因为表名必须在编译期确定，而CTE的动态表名是运行时生成的，这就是错误的核心原因。要实现基于集合的批量MERGE，需用CTE生成所有需要执行的动态SQL语句，再一次性执行，避免循环开销。

步骤1：用CTE生成所有MERGE语句脚本 #

通过CTE关联查找表，一次性生成所有ChannelID对应的MERGE语句：

WITH ChannelMergeScripts AS (
    SELECT
        dc.ChannelReadingTablename,
        ci.ChannelID,
        -- 拼接MERGE语句
        CONCAT(
            'MERGE [', dc.ChannelReadingTablename, '] T ',
            'USING (',
            'SELECT [Si] as SIReading, [Raw] as RawReading, [TimeStamp] as ReadingDateTime ',
            'FROM [#ChannelReadingMain] WHERE ChannelID = ''', ci.ChannelID, '''',
            ') S ON T.ReadingDateTime = S.ReadingDateTime ',
            'WHEN NOT MATCHED BY TARGET THEN ',
            'INSERT (ReadingDateTime, SIReading, RawReading) ',
            'VALUES (S.ReadingDateTime, S.SIReading, S.RawReading);'
        ) AS MergeScript
    FROM ChannelInfo ci
    JOIN DeviceChannel dc ON ci.ChannelID = dc.ChannelID
    -- 可添加筛选条件，比如只处理特定ChannelID
)
SELECT MergeScript FROM ChannelMergeScripts;

步骤2：批量执行生成的脚本 #

用STRING_AGG将所有MERGE语句拼接成一个完整SQL脚本，再通过sp_executesql执行：

DECLARE @BatchSQL NVARCHAR(MAX);

WITH ChannelMergeScripts AS (
    SELECT
        dc.ChannelReadingTablename,
        ci.ChannelID,
        CONCAT(
            'MERGE [', dc.ChannelReadingTablename, '] T ',
            'USING (',
            'SELECT [Si] as SIReading, [Raw] as RawReading, [TimeStamp] as ReadingDateTime ',
            'FROM [#ChannelReadingMain] WHERE ChannelID = ''', ci.ChannelID, '''',
            ') S ON T.ReadingDateTime = S.ReadingDateTime ',
            'WHEN NOT MATCHED BY TARGET THEN ',
            'INSERT (ReadingDateTime, SIReading, RawReading) ',
            'VALUES (S.ReadingDateTime, S.SIReading, S.RawReading);',
            CHAR(13) + CHAR(10) -- 换行分隔语句
        ) AS MergeScript
    FROM ChannelInfo ci
    JOIN DeviceChannel dc ON ci.ChannelID = dc.ChannelID
)
SELECT @BatchSQL = STRING_AGG(MergeScript, '')
FROM ChannelMergeScripts;

-- 执行批量脚本
EXEC sp_executesql @BatchSQL;

关键说明 #

1. 避免循环逐次执行的开销，改为一次性生成并执行所有MERGE语句
2. 绕开CTE作用域限制：CTE仅用于生成动态SQL文本，而非直接在MERGE中引用动态表名，解决了编译期表名必须确定的问题
3. 若ChannelID数量过多导致脚本长度超出NVARCHAR(MAX)限制，可按ChannelID分组分批次执行

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