SQL Server执行Merge操作仅更新指定列并规避非空约束的方法

现有代码问题说明 #

你当前的Merge语句存在逻辑冗余问题：ON子句已经限定了匹配条件为Dest.[CaseId] = Src.[CaseId]，后续WHEN MATCHED后追加的AND (Dest.[CaseId] != Src.[CaseId])条件永远不成立，该更新分支完全不会生效，可根据实际业务需求调整触发更新的判断条件。

两种更优实现方案 #

方案1：给非空列添加DEFAULT约束（推荐） #

如果你的非空列默认填充值固定为0，直接给对应字段添加默认值约束，插入时无需显式声明这些字段，数据库会自动填充默认值，可空字段也会自动赋值为NULL，大幅简化代码。

1. 先为非空列添加默认约束：

ALTER TABLE [tempdb].[dbo].[#Correspondence] 
ADD 
CONSTRAINT DF_Correspondence_RecipientAddressID DEFAULT 0 FOR [RecipientAddressID],
CONSTRAINT DF_Correspondence_Faxed DEFAULT 0 FOR [Faxed],
CONSTRAINT DF_Correspondence_CertifiedMail DEFAULT 0 FOR [CertifiedMail],
-- 其余需要填充0的非空列按上述格式补全即可

2. 简化后的Merge语句：

MERGE INTO [tempdb].[dbo].[#Correspondence] Dest
USING [tempdb].[dbo].[#toBeTransferred] Src
ON (Dest.[CaseId] = Src.[CaseId])
WHEN MATCHED -- 这里补充你实际需要的更新触发条件，比如某字段不一致
THEN UPDATE 
    SET 
        -- 仅列出你需要更新的4个字段即可
        Dest.[Description] = Src.[Description],
        Dest.[Date] = Src.[Date],
        Dest.[Link] = Src.[Link]
WHEN NOT MATCHED THEN
    -- 仅声明你需要自定义赋值的4个字段
    INSERT ([CaseId], [Description], [Date], [Link])
    VALUES (Src.[CaseId], Src.[Description], Src.[Date], Src.[Link]);

方案2：动态生成插入语句（适用于无法加默认约束的场景） #

如果不允许修改表结构加默认约束，可以通过系统表查询所有需要自动填充0的非空列，动态拼接SQL语句，避免手动逐个写0出现漏写、错写问题：

DECLARE @InsertCols NVARCHAR(MAX), @InsertVals NVARCHAR(MAX)
-- 拼接需要插入的列名
SELECT @InsertCols = COALESCE(@InsertCols + ', ', '') + QUOTENAME(name)
FROM tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb.dbo.#Correspondence')
AND name IN ('CaseId', 'Description', 'Date', 'Link') -- 你的自定义赋值列
UNION ALL
SELECT @InsertCols = COALESCE(@InsertCols + ', ', '') + QUOTENAME(name)
FROM tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb.dbo.#Correspondence')
AND is_nullable = 0 -- 非空列
AND name NOT IN ('CaseId', 'Description', 'Date', 'Link') -- 排除自定义赋值列

-- 拼接插入值
SELECT @InsertVals = COALESCE(@InsertVals + ', ', '') + 'Src.' + QUOTENAME(name)
FROM tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb.dbo.#Correspondence')
AND name IN ('CaseId', 'Description', 'Date', 'Link')
UNION ALL
SELECT @InsertVals = COALESCE(@InsertVals + ', ', '') + '0'
FROM tempdb.sys.columns 
WHERE object_id = OBJECT_ID('tempdb.dbo.#Correspondence')
AND is_nullable = 0
AND name NOT IN ('CaseId', 'Description', 'Date', 'Link')

-- 执行动态Merge
DECLARE @Sql NVARCHAR(MAX) = N'
MERGE INTO [tempdb].[dbo].[#Correspondence] Dest
USING [tempdb].[dbo].[#toBeTransferred] Src
ON (Dest.[CaseId] = Src.[CaseId])
WHEN MATCHED 
THEN UPDATE 
    SET 
        Dest.[Description] = Src.[Description],
        Dest.[Date] = Src.[Date],
        Dest.[Link] = Src.[Link]
WHEN NOT MATCHED THEN
    INSERT (' + @InsertCols + N')
    VALUES (' + @InsertVals + N');
'
EXEC sp_executesql @Sql

注意事项 #

• 关联键CaseId如果是主键/唯一键，不建议在更新分支修改该字段值，容易引发数据冲突或逻辑混乱
• 如果需要更新的4个字段有其他校验要求，可在UPDATE分支自行补充赋值逻辑

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