I thought you knew some command shorthand I didn't know, but it doesn't appear that way. As far as I know, the MariaDB/MySQL command is INSERT ON DUPLICATE KEY UPDATE which is a lot more long-winded than UPSERT, but does the same thing.
And I also highly recommend using an upsert/on conflict/on duplicate command, whatever it happens to be in your SQL server.
(EDIT: And, yes, the MariaDB ON DULICATE KEY has some weird caveats. The only one that has bit me bad enough to keep me nervous is if you are working on a table with more than one key column and your INSERT hits matching keys on two different rows...)
Did you try writing a SQL query that has an if statement in it? We using this style in our DB (MSSQL) to avoid duplicate entries while updating existing entries.
IF EXISTS(SELECT ArrayID FROM TGMS_LookupBuilding WHERE ArrayID=?)
UPDATE TGMS_LookupBuilding SET Name=? WHEREArrayID=?
INSERT INTO TGMS_LookupBuilding(Name, ArrayID) VALUES(?, ?)
Hmm, guess I'm not completely sure on where something is a SQL script vs a query. I've only ever worked with MSSQL with low volume of data coming through, which probably doesn't help. That's probably why everything I've done so far has 'Just Worked'™
Any BEGIN or DECLARE or anything that would use more than one GO in SSMS is a script. @variable assignments are parts of scripts. Any statement that nests another statement where the other statement is allowed to be BEGIN is also a SQL script element.
MS SQL Server has so many solutions that require scripts that it needs SQL script support to not look utterly lame in the JDBC world. IMNSHO.
I don't put SQL Server in the same category as Windows itself, mainly because it can be (and should be) installed on Linux, but also because its high availability clustering seems to work very well (as seen with a few of my clients).
I place MS Windows in the "Engineering Malpractice" category when used anywhere in production networks. ↩︎
I suspect it would come into play if the query was executing from potentially multiple sources. So that if 2 sources ran at the same time with an ID field value of 3 either both could be inserted ending up with 2 rows with ID of 3 and probably some other issues.
For my use case that isn't an issue since there is only one thing that can run the script so there isn't any way to have overlapping requests.
Well, if you can run INSERT and UPDATE separately, and you have a unique key (or primary key) present to prevent duplicates, just run the INSERT first, in a try: block. If it fails, run the update instead. Check the return value from either to make sure you got one row affected, or queue up a retry.
If you are mostly inserting, optimistically running it will be a win.