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.