Skip to Content
avatar image
Former Member

Are MERGE statements in stored procedures supported by SAP HANA

I am in process of migrating database objects from SQL SERVER to SAP HANA. I have observed that HANA SQL documentation supports MERGE INTO syntax. However, in one of the procedures where I am trying to use MERGE statement inside an autonomous transaction, I am getting a SQL syntax error.

My statement looks like this :

BEGIN AUTONOMOUS TRANSACTION

MERGE INTO <schema>.<table> USING(SELECT statement inside) ON (criteria) WHEN MATCHED THEN update set <statements> WHEN NOT MATCHED THEN INSERT <STATEMENTS>;

COMMIT;

END;

The error looks like "SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "INTO": line 6 col 15 (at pos 422)"

Also, what is the equivalent of XACT_STATE() function in HANA.

TIA

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    May 15, 2017 at 04:20 PM

    Hi Sachin,

    It depends on your system version. Looks like it is supported from HANA 2.0

    https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.01/en-US/3226201f95764a57810dd256c9524d56.html

    if your system is not there yet, then look for UPSERT statement in HANA. This acts as both INSERT & UPDATE. If the source record matches based on table primary key then it updates the record otherwise inserts it.

    Following url should provide information about UPSERT syntax:

    https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.01/en-US/20fc06a7751910149892c0d09be21a38.html

    Regards,

    Venkat

    Add comment
    10|10000 characters needed characters exceeded