如何删除Azure Synapse无服务器池中所有表的第一行

报错DML Operations are not supported with external tables的原因是：Azure Synapse的外部表仅作为外部存储（如ADLS Gen2、Blob存储）中数据的映射，不支持直接执行DELETE、UPDATE等DML操作。要实现删除第一行的需求，需通过「数据导出-处理-重新导入」的流程完成，以下是具体实现步骤：

核心思路 #

将外部表的数据导入内部表（专用SQL池/无服务器SQL池的表），过滤掉第一行后，重新生成外部表覆盖原存储路径的文件。

步骤1：明确「第一行」的定义 #

必须指定用于确定行顺序的列（如自增ID、时间戳等），否则「第一行」是无意义的随机行。假设你用ID列排序来确定第一行。

步骤2：将外部表数据导入内部表（排除第一行） #

-- 创建内部表并复制排除第一行的数据
SELECT * INTO [dbo].[MyTable_Internal]
FROM [dbo].[MyTable]
ORDER BY ID OFFSET 1 ROWS;

• 若使用无服务器SQL池，可创建临时表：SELECT * INTO #MyTable_Internal ...

步骤3：替换外部存储中的原数据 #

方法A：使用CETAS重新生成外部表

-- 先删除原外部表（可选，若需保留表名可后续重命名）
DROP TABLE IF EXISTS [dbo].[MyTable];

-- 通过CETAS生成新的外部表，覆盖原存储路径
CREATE EXTERNAL TABLE [dbo].[MyTable]
WITH (
    LOCATION = 'your-container/your-folder/', -- 原外部表的存储路径
    DATA_SOURCE = YourExternalDataSource, -- 原外部数据源名称
    FILE_FORMAT = YourFileFormat -- 原外部表使用的文件格式（如Parquet、CSV）
)
AS SELECT * FROM [dbo].[MyTable_Internal];

方法B：清空原存储文件后导入（适用于需保留原表结构的场景）

1. 使用Azure CLI/PowerShell清空原存储路径的文件：

# Azure CLI 示例：删除ADLS Gen2路径下的所有文件
az storage fs file delete --account-name your-storage-account --file-system your-container --path your-folder --recursive

2. 再通过INSERT INTO EXTERNAL TABLE将内部表数据写入：

INSERT INTO [dbo].[MyTable]
SELECT * FROM [dbo].[MyTable_Internal];

步骤4：清理临时资源 #

DROP TABLE IF EXISTS [dbo].[MyTable_Internal];
-- 若使用临时表则无需此步骤

批量处理多个表 #

如果需要处理多个外部表，可使用游标遍历表名批量执行：

DECLARE @TableName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

-- 定义游标遍历目标外部表
DECLARE TableCursor CURSOR FOR
SELECT name FROM sys.tables WHERE schema_id = SCHEMA_ID('dbo') AND is_external = 1; -- 筛选外部表

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成创建内部表的SQL
    SET @SQL = N'
        SELECT * INTO [dbo].[' + @TableName + '_Internal]
        FROM [dbo].[' + @TableName + ']
        ORDER BY ID OFFSET 1 ROWS; -- 替换为你的排序列
    ';
    EXEC sp_executesql @SQL;

    -- 生成重新创建外部表的SQL（需提前确认所有外部表的数据源、文件格式一致，或动态获取）
    SET @SQL = N'
        DROP TABLE IF EXISTS [dbo].[' + @TableName + '];
        CREATE EXTERNAL TABLE [dbo].[' + @TableName + ']
        WITH (
            LOCATION = ''your-container/' + @TableName + '/'', -- 假设每个表对应单独的存储路径
            DATA_SOURCE = YourExternalDataSource,
            FILE_FORMAT = YourFileFormat
        )
        AS SELECT * FROM [dbo].[' + @TableName + '_Internal];
    ';
    EXEC sp_executesql @SQL;

    -- 清理临时表
    SET @SQL = N'DROP TABLE IF EXISTS [dbo].[' + @TableName + '_Internal];';
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

注意事项 #

• 确保Synapse工作区对外部存储账户拥有读写权限（如Storage Blob Data Contributor角色）。
• 若外部表数据量极大，建议使用Synapse Pipeline进行批量处理，避免SQL池资源占用过高。
• 必须指定排序列，否则无法保证删除的是预期的「第一行」。

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