ParseException when MERGE is used in a statement

[badapinguino]

Hello everyone,

I'm trying to parse a statement to get the target and source tables and save them into a file.
I already did it successfully with INSERT and UPDATE, also when there are joins, unions, subqueries, and WITHs.
Unfortunately, I'm having issues with the MERGE clause.
For example, I have the following MERGE statement:

WITH
        WMachine AS
        (   SELECT
                DISTINCT 
                ProjCode,
                PlantCode,
                BuildingCode,
                FloorCode,
                Room
            FROM
                TAB_MachineLocation
            WHERE
                TRIM(Room) <> '' AND TRIM(Room) <> '-'
        )
    MERGE
        TAB_RoomLocation AS TRoom
    USING
        WMachine
    ON
        (
            TRoom.ProjCode = WMachine.ProjCode
        AND TRoom.PlantCode = WMachine.PlantCode
        AND TRoom.BuildingCode = WMachine.BuildingCode
        AND TRoom.FloorCode = WMachine.FloorCode
        AND TRoom.Room = WMachine.Room)
    WHEN NOT MATCHED BY TARGET THEN
    INSERT
        (
            ProjCode,
            PlantCode,
            BuildingCode,
			FloorCode,
            Room
        )
        VALUES
        (
            WMachine.ProjCode,
            WMachine.PlantCode,
            WMachine.BuildingCode,
            WMachine.FloorCode,
            WMachine.Room
        )
        OUTPUT GETDATE() AS TimeAction,
        $action as Action,
        INSERTED.ProjCode,
        INSERTED.PlantCode,
        INSERTED.BuildingCode,
        INSERTED.FloorCode,
        INSERTED.Room
    INTO
        TAB_MergeActions_RoomLocation

It works fine on SQL Server but when I try to parse it with jsqlparser with the function CCJSqlParserUtil.parse(statement) it throws a net.sf.jsqlparser.parser.ParseException, that says:

Encountered unexpected token: "TAB_RoomLocation" <S_IDENTIFIER>
    at line 16, column 9.

Was expecting:

    "INTO"

Why does jsqlparser expect an INTO after the MERGE keyword? My SQL code works fine on SQL Server.

Thank you in advance for your help.

[manticore-projects]

Greetings,

your syntax is not SQL:2016 compliant.
Please see the supported synatx for the Merge Statement here: http://217.160.215.75:8080/jsqlformatter/JSQLParser/syntax_snapshot.html#merge

[manticore-projects]

You can also test your statement online here.

[manticore-projects]

You SQL:2016 compliant Merge would look like this:

MERGE INTO tab_roomlocation AS troom
    USING wmachine
        ON ( troom.projcode = wmachine.projcode
                AND troom.plantcode = wmachine.plantcode
                AND troom.buildingcode = wmachine.buildingcode
                AND troom.floorcode = wmachine.floorcode
                AND troom.room = wmachine.room )
WHEN NOT MATCHED THEN
    INSERT ( projcode
                , plantcode
                , buildingcode
                , floorcode
                , room )
    VALUES ( wmachine.projcode
                , wmachine.plantcode
                , wmachine.buildingcode
                , wmachine.floorcode
                , wmachine.room )
;

We do not support the Output clause for the MergeInsert.

[badapinguino]

Hello,

Thank you for your answers and the code example.

I tested the code with the link you sent me and it shows me an error, as you said.
It's strange that it's unsupported because I ran this code biweekly in a stored procedure on SQL Server 2019, and it never showed me any error and it works as expected.
Maybe it's a syntax supported only in SQL Server 2019 and not in 2016?
Anyway thank you for specifying that the OUTPUT clause is not supported, it's a pity but I will keep that in mind.

I will configure my program to accept the Merge statements with the syntax you showed me.

Have a nice day.

[manticore-projects]

Greetings!

I have added support for WITH ... and the Output Clause.
You can test your statement online here.

WITH wmachine AS (
        SELECT DISTINCT
            projcode
            , plantcode
            , buildingcode
            , floorcode
            , room
        FROM tab_machinelocation
        WHERE Trim( Room ) <> ''
            AND Trim( Room ) <> '-' )
MERGE INTO tab_roomlocation AS troom
    USING wmachine
        ON ( troom.projcode = wmachine.projcode
                AND troom.plantcode = wmachine.plantcode
                AND troom.buildingcode = wmachine.buildingcode
                AND troom.floorcode = wmachine.floorcode
                AND troom.room = wmachine.room )
WHEN NOT MATCHED /* BY TARGET */ THEN
    INSERT ( projcode
                , plantcode
                , buildingcode
                , floorcode
                , room )
    VALUES ( wmachine.projcode
                , wmachine.plantcode
                , wmachine.buildingcode
                , wmachine.floorcode
                , wmachine.room )
OUTPUT  Getdate() AS timeaction
        , $action AS action
        , inserted.projcode
        , inserted.plantcode
        , inserted.buildingcode
        , inserted.floorcode
        , inserted.room
    INTO tab_mergeactions_roomlocation
;

[badapinguino]

Wow, that's great! Thank you so much.

I will test it when I finish implementing the MERGE management in my program.
And I will change the MERGE syntax used in my store procedure to the one supported by you.

Thank you again and have a great day.