Skip to Content
0

Are MERGE statements in stored procedures supported by SAP HANA

May 12, 2017 at 08:48 AM

166

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

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

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

Share
10 |10000 characters needed characters left characters exceeded