I wrote code to upsert records to the database. The database is Azure DB, S4 tier.
Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many updates.
The code works successfully most of the times, but sometimes it throws 2627 exception (that handled inside of the CATCH statement). Cannot figure out so far what could be the issue.
I can replace it with MERGE statement, but I tbh don't favor it much, and also want to figure out what's happening with this try and catch.
SET nocount ON; IF NOT EXISTS(SELECT * FROM MyTable WHERE ID = @ID) BEGIN TRY INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE()) END TRY BEGIN CATCH IF @@Error <> 2627 ----Violation of PRIMARY KEY constraint THROW; END CATCH UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()