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.