MSSQL MERGE with runPrepUpdate

Ignition
1

Hi,

I have written a MSSQL MERGE query and run it using runPrepUpdate with getKey flag set to 1. I want to know the affected Primary Key in the Target table. I have to use it further in the code.
The runPrepUpdate and MERGE works fine for both Insert and Update.
It also returns key but returning key is very unpredictable.
After I run the same statement a few times (like 10), instead of returning the correct key, it starts giving me a random number like 4692722 or 4692723 or something else which is not the primary key value.

This behavior is concerning for me.
I want to understand why would it not return the right key always?
Any help in this regard is much appreciated.

I am using script console to test this.

Thanks,
Ajay

2

Can you share the full query?

3

I would guess that MERGE, because it doesn't unconditionally perform an insert, is not implemented with the same atomicity as INSERT itself. (IIRC, this was mentioned as an advantage of PostgreSQL's standards-compliant ON CONFLICT clause over MERGE behavior.) You will very likely need a stored procedure to make this reliable. Perhaps you could switch to a better database....

3 Likes