MERGE语句无法插入数据且报标识列更新错误的修复请求

错误原因分析 #

报错Cannot update identity column 'customer_skey'属于误导性提示，真实触发原因如下：

• 你在WHEN MATCHED的UPDATE逻辑中修改了customer_number列，而该列是表的HASH分布键。在Azure Synapse Analytics中，HASH分布表的分布列不允许被UPDATE，修改分布列会触发数据跨节点重分布，底层逻辑因此抛出这个看似和identity列相关的错误。
• 不必要地开启了IDENTITY_INSERT，虽然这不是直接报错原因，但插入操作未指定customer_skey（identity列），开启该设置完全多余。

修复后的MERGE语句 #

MERGE INTO dim.customer_crm AS tgt
USING (SELECT 
           accountnumber, name, 
           ISNULL(address1_line1, '') + ',' + ISNULL(address1_line2, '') + ', ' + ISNULL(address2_city,'') AS address,   
           LEFT(address1_postalcode, 5) AS post_code, 
           address1_stateorprovince, 
           address2_country AS country, 
           emailaddress1 AS email, 
           new_abn AS abn, 
           telephone1 AS telephone,
           CASE WHEN parentaccountid IS NULL THEN 'n' ELSE 'y' END AS is_parent
       FROM
           src.crm_account a
       WHERE 
           statuscode = 1) src ON (tgt.customer_number = src.accountnumber)
        
WHEN MATCHED THEN
    UPDATE 
    SET 
        customer_name = src.Name,
        customer_address = src.address,
        post_code = src.post_code,
        state = src.address1_stateorprovince,
        country = src.country,
        email = src.email,
        abn = src.abn,
        contact_no = src.telephone,
        is_parent = src.is_parent,
        dw_insert_timestamp = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (customer_number, customer_name, customer_address, post_code, state, country, email, abn, contact_no, is_parent, dw_insert_timestamp)
    VALUES (src.accountnumber, src.name, src.address, src.post_code, src.address1_stateorprovince, src.country, src.email, src.abn, src.telephone, src.is_parent, GETDATE());

关键修复点说明 #

• 移除分布列的更新：删除了UPDATE中的customer_number = src.accountnumber，匹配条件已保证两者值一致，且分布列不允许更新。
• 删除冗余的IDENTITY_INSERT设置：插入时未指定customer_skey，数据库会自动生成identity值，无需开启该设置。
• 补充遗漏的列处理：补上原语句中未涉及的state列的更新与插入；同时为dw_insert_timestamp添加赋值逻辑（插入和更新时设置为当前时间，可根据实际需求调整）。

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