Synapse SQL合并临时表至主表时自增主键实现问题咨询

方案1：拆分MERGE为UPDATE+INSERT（最推荐，规避IDENTITY列操作限制） #

Azure Synapse 专用SQL池的MERGE语句不支持对IDENTITY自增列执行隐式插入，最稳妥的修复方式是把合并操作拆分为更新、插入两步执行，完全避开MERGE的限制：

-- 第一步：执行匹配行的更新操作
UPDATE t
SET t.name = s.name
FROM [table1] t
JOIN [table1_staging] s ON t.id = s.id

-- 第二步：插入未匹配的新行，无需指定IDENTITY列，系统自动生成主键
INSERT INTO [table1] ([id], [name])
SELECT s.id, s.name
FROM [table1_staging] s
LEFT JOIN [table1] t ON s.id = t.id
WHERE t.id IS NULL

这个方案不需要修改原有表结构，完全兼容你已经创建的IDENTITY自增主键逻辑，性能也比直接用MERGE更适合Synapse分布式架构。

方案2：手动维护自增主键，兼容MERGE语句 #

如果你必须使用MERGE操作，可以改用官方推荐的INT PRIMARY KEY NONCLUSTERED NOT ENFORCED主键，自己生成自增ID值：

步骤1：修改表结构，替换IDENTITY列为普通主键列 #

CREATE TABLE [table1]
  (  
    [primaryKey] INT PRIMARY KEY NONCLUSTERED NOT ENFORCED,
    [id] INT,
    [name] VARCHAR(25)
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX,
DISTRIBUTION = HASH([id])
);

步骤2：MERGE时动态生成新主键 #

-- 先计算当前表最大主键值
DECLARE @max_id INT = (SELECT ISNULL(MAX(primaryKey),0) FROM [table1])

MERGE [table1] AS TARGET
USING (
    -- 给 staging 表的新数据生成主键：已有ID的保留原主键，新增的用最大ID+行号
    SELECT 
        s.id,
        s.name,
        t.primaryKey as existing_pk,
        @max_id + ROW_NUMBER() OVER(ORDER BY s.id) as new_pk
    FROM [table1_staging] s
    LEFT JOIN [table1] t ON s.id = t.id
) AS SOURCE 
ON (TARGET.id = SOURCE.id)
-- 匹配到的更新
WHEN MATCHED THEN UPDATE SET TARGET.name = SOURCE.name
-- 未匹配的插入，直接传入生成好的主键
WHEN NOT MATCHED BY TARGET 
THEN INSERT ([primaryKey], [id], [name]) VALUES(SOURCE.new_pk, SOURCE.[id], SOURCE.[name]);

注意事项 #

• 手动生成主键的场景需要自行保障主键唯一性，避免并发写入导致的ID冲突
• Synapse的IDENTITY列本身是分布式生成的，不保证数值严格连续，如果你需要连续的主键值，只能用手动维护的方案

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