Azure Synapse无服务器SQL池存储过程遍历查询结果的替代方案

问题背景 #

将SQL专用池的存储过程迁移到Azure Synapse无服务器SQL池时，原逻辑是将查询结果存入临时表后逐行遍历，但无服务器SQL池不支持临时表，运行时触发以下错误：

Msg 15816, Level 16, State 2, Procedure test.test_temp_table, Line 7
The query references an object that is not supported in distributed processing mode.

已尝试的临时表创建方式均无效，包括：

CREATE TABLE #temp_table
WITH
( DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
FIELD1, FIELD2, FIELD3...
FROM OPENROWSET(
    BULK '<delta-table path>',
    FORMAT = 'DELTA',
    DATA_SOURCE = '<ds-name>'
) AS source

CREATE TABLE #temp_table
WITH
( DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
FIELD1, FIELD2, FIELD3...
FROM [<schema>].[<view_name>]

CREATE TABLE #temp_table
WITH
( DISTRIBUTION = ROUND_ROBIN)
AS
SELECT FIELD1, FIELD2, FIELD3...
FROM [<schema>].[<view_name>]

CREATE TABLE #temp_table (
    FIELD1 VARCHAR(1020),
    FIELD2 VARCHAR(80)
)

INSERT INTO #temp_table
SELECT FIELD1, FIELD2
FROM OPENROWSET(
        BULK '<delta-table path>',
        FORMAT = 'DELTA',
        DATA_SOURCE = '<ds-name>'
) AS source

可行替代方案 #

1. 表变量替代临时表 #

无服务器SQL池支持表变量，可存储查询结果后通过游标或循环遍历：

DECLARE @temp_table TABLE (
    Sequence INT,
    FIELD1 VARCHAR(1020),
    FIELD2 VARCHAR(80),
    FIELD3 ...
);

INSERT INTO @temp_table
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
       FIELD1, FIELD2, FIELD3...
FROM OPENROWSET(
    BULK '<delta-table path>',
    FORMAT = 'DELTA',
    DATA_SOURCE = '<ds-name>'
) AS source;

-- 遍历表变量
DECLARE @seq INT, @f1 VARCHAR(1020), @f2 VARCHAR(80);
DECLARE cur CURSOR FOR SELECT Sequence, FIELD1, FIELD2 FROM @temp_table;
OPEN cur;
FETCH NEXT FROM cur INTO @seq, @f1, @f2;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 自定义处理逻辑
    PRINT CONCAT('Sequence: ', @seq, ', FIELD1: ', @f1);
    
    FETCH NEXT FROM cur INTO @seq, @f1, @f2;
END;
CLOSE cur;
DEALLOCATE cur;

2. 集合式操作替换逐行遍历 #

尽量避免逐行循环，改用集合查询实现业务逻辑。例如原遍历更新逻辑可替换为JOIN操作：

UPDATE target_table
SET target_col = source.FIELD2
FROM target_table t
JOIN (
    SELECT FIELD1, FIELD2
    FROM OPENROWSET(
        BULK '<delta-table path>',
        FORMAT = 'DELTA',
        DATA_SOURCE = '<ds-name>'
    ) AS src
) source ON t.id = source.FIELD1;

3. CTE串联分步逻辑 #

如果遍历是为了分步处理数据，可将查询结果放入CTE，后续逻辑直接基于CTE操作：

WITH data_cte AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
           FIELD1, FIELD2, FIELD3...
    FROM OPENROWSET(
        BULK '<delta-table path>',
        FORMAT = 'DELTA',
        DATA_SOURCE = '<ds-name>'
    ) AS source
)
-- 基于CTE的后续处理
SELECT * FROM data_cte WHERE Sequence BETWEEN 1 AND 10;

4. 外部表持久化中间结果 #

若需多次复用查询结果，可创建外部表存储中间数据，再基于外部表遍历：

-- 创建外部表
CREATE EXTERNAL TABLE ext_temp_table (
    Sequence INT,
    FIELD1 VARCHAR(1020),
    FIELD2 VARCHAR(80)
)
WITH (
    LOCATION = 'temp_data/',
    DATA_SOURCE = '<ds-name>',
    FILE_FORMAT = '<file-format>'
);

-- 插入数据
INSERT INTO ext_temp_table
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
       FIELD1, FIELD2
FROM OPENROWSET(
    BULK '<delta-table path>',
    FORMAT = 'DELTA',
    DATA_SOURCE = '<ds-name>'
) AS source;

-- 遍历外部表（游标逻辑同表变量示例）
DECLARE cur CURSOR FOR SELECT Sequence, FIELD1 FROM ext_temp_table;
OPEN cur;
-- 处理逻辑...
CLOSE cur;
DEALLOCATE cur;

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