SQL Server Update query output clause values into another table from within merge query

hwazgwia 于 12个月前发布在其他

关注(0)|答案(1)|浏览(82)

Here is my simple merge query. If no match found in USER profile table, it will insert new records for PUB, PVT. If exist it will update statuses. Now I want to use output clause to get updated value and move into history table.

But I am getting syntax error
A MERGE statement must be terminated by a semi-colon (;) just before WHEN NOT MATCHED.

MERGE [dbo].[USER_PROFILE] AS target
USING (
    VALUES (130, 'PUB'), (130, 'PVT')
) AS source(UserId, PROFILE)
ON target.UserId = source.UserId AND PROFILE = PROFILE
    WHEN MATCHED THEN
        UPDATE SET 
            Verified = 1,
            Created = 1
        OUTPUT 
            INSERTED.UserId, 
            INSERTED.Verified, 
            INSERTED. Created, 
  INTO [history].[USER_PROFILE] (UserId, Verified, Created)
    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].[Profile] (UserId, Profile, Created, Verified);

sql-server

来源：https://stackoverflow.com/questions/77669941/update-query-output-clause-values-into-another-table-from-within-merge-query

1条答案

按热度按时间

flvlnr441#

A MERGE can only have a single OUTPUT clause, and it must go at the end. You would have seen this if you would consult the documentation.

MERGE dbo.USER_PROFILE AS target
USING (VALUES
    (130, 'PUB'),
    (130, 'PVT')
) AS source (UserId, PROFILE)
ON target.UserId = source.UserId AND source.PROFILE = target.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.Profile
        (UserId, Profile, Created, Verified);

It's also clear from your comments that this output table is a history table of a temporal table. You don't insert into that directly, the system does it for you.

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Update query output clause values into another table from within merge query

1条答案

相关问题

热门标签

最新问答