Synapse SQL创建动态SQL出现子查询多行错误，普通SQL正常求排查

错误根源分析 #

你遇到的"子查询返回多个值"错误，核心和代码里的几个明显缺陷有关：

1. @SourceColNames赋值逻辑错误：如果直接用子查询SELECT col_name FROM #TempTable给变量赋值，会返回多条列名记录，但单个变量只能接收单个值，直接触发报错。
2. 循环逻辑完全失效：你的循环没有写SET @Min_cnt = @Min_cnt + 1的递增语句，会陷入死循环；而且循环内完全没有拼接列名的逻辑，根本没法生成需要的列名字符串。
3. SQL语法多处错误：初始INSERT语句多了一个右括号，且INFORMATION_SCHEMA.COLUMNS中对应schema的字段是TABLE_SCHEMA而非SCHEMA_NAME；动态SQL里的CTE定义不完整，CTE与后续UPDATE的结构衔接错误。
4. 未执行动态SQL：原代码最后仅用SELECT @DySQL_DUP查看语句，没有实际执行逻辑。

修正后的完整代码 #

1. 创建临时表并插入列名

CREATE TABLE #TempTable (
    ID int IDENTITY(1,1),
    col_name varchar(128)
);

INSERT INTO #TempTable 
SELECT COLUMN_NAME AS col_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'table_1' AND TABLE_SCHEMA = 'dbo';

2. 高效拼接列名字符串

DECLARE @SourceColNames varchar(max);
-- 用Synapse支持的STRING_AGG直接拼接成逗号分隔的字符串，替代低效循环
SET @SourceColNames = (SELECT STRING_AGG(col_name, ', ') FROM #TempTable);

3. 构建并执行动态SQL

DECLARE @DySQL_DUP nvarchar(max);
SET @DySQL_DUP = N';WITH Duplicate AS (
    SELECT ID, 
           ROW_NUMBER() OVER(PARTITION BY ' + @SourceColNames + ' ORDER BY ' + @SourceColNames + ') AS ROW_COUNT 
    FROM dbo.table_1 
    WHERE FileLoadLogID = 1
)
UPDATE dbo.table_1 
SET col_to_update = ''DUPLICATE_ROWS''
WHERE ID IN (
    SELECT TARGET.ID 
    FROM dbo.table_1 TARGET
    INNER JOIN Duplicate ON TARGET.ID = Duplicate.ID
    WHERE Duplicate.ROW_COUNT > 1
)';

-- 执行动态SQL
EXEC sp_executesql @DySQL_DUP;

关键说明 #

• Synapse SQL支持STRING_AGG函数，用它拼接列名比循环高效得多，也避免了循环逻辑出错的问题。
• 动态SQL必须通过sp_executesql执行，仅用SELECT查看语句无法完成更新操作。
• 注意INFORMATION_SCHEMA.COLUMNS的字段规范，schema对应的字段是TABLE_SCHEMA，不是SCHEMA_NAME。

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