MERGE语句用OUTPUT子句写入历史表时遇语法错误求助

你的MERGE语句报错核心问题是SQL Server不允许在MERGE的每个WHEN分支后单独定义OUTPUT子句，只能在整个MERGE语句末尾添加一个全局OUTPUT；另外原语句还有几处语法细节错误，比如匹配条件写错、列末尾多余逗号、OUTPUT列与历史表列顺序不匹配。

修正后的完整代码 #

MERGE [dbo].[USER_PROFILE] AS target
USING (
    VALUES (130, 'PUB'), (130, 'PVT')
) AS source(UserId, PROFILE)
ON target.UserId = source.UserId AND target.Profile = source.Profile
WHEN MATCHED THEN
    UPDATE SET 
        Verified = 1,
        Created = 1
WHEN NOT MATCHED THEN
    INSERT (UserId, Profile, Verified, Created)
    VALUES (source.UserId, source.Profile, 0, 0)
OUTPUT 
    INSERTED.UserId,
    INSERTED.Profile,
    INSERTED.Verified,
    INSERTED.Created
INTO [history].[USER_PROFILE] (UserId, Profile, Verified, Created);

关键修改点说明 #

• 移除了两个WHEN分支内的单独OUTPUT，改为在MERGE语句整体末尾添加一个全局OUTPUT，这是SQL Server对MERGE语句的强制语法要求
• 修正ON子句匹配条件：把PROFILE = PROFILE改成target.Profile = source.Profile，原写法是无意义的自匹配，无法正确关联源表和目标表
• 调整OUTPUT列顺序，与历史表[history].[USER_PROFILE]的列定义一一对应，避免插入时列不匹配错误
• 删除列末尾多余的逗号（比如原代码中INSERTED.Created,的尾逗号）
• 确保整个MERGE语句以分号结尾，符合SQL Server语法规范

如果需要区分更新和插入的操作类型，可以在OUTPUT中加入$action字段标记操作类型，示例如下：

MERGE [dbo].[USER_PROFILE] AS target
USING (
    VALUES (130, 'PUB'), (130, 'PVT')
) AS source(UserId, PROFILE)
ON target.UserId = source.UserId AND target.Profile = source.Profile
WHEN MATCHED THEN
    UPDATE SET 
        Verified = 1,
        Created = 1
WHEN NOT MATCHED THEN
    INSERT (UserId, Profile, Verified, Created)
    VALUES (source.UserId, source.Profile, 0, 0)
OUTPUT 
    $action AS OperationType,
    INSERTED.UserId,
    INSERTED.Profile,
    INSERTED.Verified,
    INSERTED.Created
INTO [history].[USER_PROFILE] (OperationType, UserId, Profile, Verified, Created);

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