Solutions for INSERT OR UPDATE on SQL Server

Transactions for Atomicity and Thread Safety

Maintaining data integrity is crucial while dealing with data modifications. Creating transaction boundaries around INSERT or UPDATE operations ensures atomicity and thread safety.

For instance:

BEGIN TRANSACTION;
BEGIN TRY
    -- Laying out the red carpet for an INSERT or UPDATE
    IF EXISTS (SELECT 1 FROM Target WHERE ID = @ID)
    BEGIN
        UPDATE Target
        SET Data = @Data
        WHERE ID = @ID;   -- "Hey there, I found you. Let's update!"
    END
    ELSE
    BEGIN
        INSERT INTO Target (ID, Data)
        VALUES (@ID, @Data);  -- "New kid on the block? Welcome!"
    END

    -- Record updated, check if an Insert is required. 
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO Target (ID, Data)
        VALUES (@ID, @Data);  -- "Seems no one was updated. Time to Insert!"
    END;

    COMMIT TRANSACTION;  -- "Job's done, let's wrap up."
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;  --"Oopsie, things went south. Let's rollback."
END CATCH;


Alternate Path: Use IF EXISTS Instead of MERGE

The MERGE command is king-size bed, but not always comfortable. An alternative is to conduct a conditional IF EXISTS check - more suited for high concurrency or particular constraint situations.

Consider this best practice:

IF EXISTS (SELECT 1 FROM Target WHERE ID = @ID)
BEGIN
    UPDATE Target
    SET Data = @Data
    WHERE ID = @ID;   -- "Found my match. It's update time!"
END
ELSE
BEGIN
    INSERT INTO Target (ID, Data)
    VALUES (@ID, @Data);  -- "New here? Let's insert you."
END


Dealing with Concurrency: Locks to the Rescue

Concurrency can lead to conflicts and deadlocks. Using updlock and holdlock (or serializable) can explicitly lock resources during a transaction, preventing other transactions from modifying those resources until the lock is dismissed.

MERGE INTO Target WITH (HOLDLOCK) AS T
USING Source AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Data = S.Data  -- "It's a match! Let's update."
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Data) VALUES (S.ID, S.Data);  -- "No match? Let's insert."


By guarding the target table with a HOLDLOCK, you substantially lower the risk of encountering concurrency issues during the merge.

Digging Deeper: The UPSERT Approach

Although the MERGE statement is comprehensive, it should be handled with care. An UPSERT pattern can blend the best of UPDATE and INSERT based on @@ROWCOUNT for control and specificity.

UPDATE Target SET Data = @Data WHERE ID = @ID;  -- "Alright, let's update this.
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Target (ID, Data) VALUES (@ID, @Data);  -- "Looks like no update. Insert it is.”
END


Although this pattern is more verbose, it provides precise control over UPSERT. However, avoid using this pattern in highly concurrent situations without appropriate locking or isolation levels.