Best approaches for UPSERT operations in SQL Server database

Data and Databases
1

I’m working with a database table that has this basic structure: UserData(ID, name, email, status).

I frequently need to perform operations where I either modify an existing row or create a new one if no matching record is found.

The logic I’m trying to implement looks something like this:

IF (record with ID exists)
  perform UPDATE operation
ELSE
  perform INSERT operation

I’ve been wondering what would be the most efficient method to handle this type of operation in SQL Server. Are there any built-in features or specific techniques that work better than others for this scenario?

I’m particularly interested in performance considerations since this operation will be executed frequently in my application.

2

insert…on duplicate key won’t work in sql server - that’s mysql syntax. for sql server, try using the try_update_first pattern. update first, check @@rowcount, then insert if nothin’ updated. often faster than merge for single rows since merge can be overkill.

3

interesting question! have u tried INSERT ... ON DUPLICATE KEY or benchmarked MERGE vs a basic UPDATE/INSERT combo? what’s ur table size like, and r u working with single records or batches? that’ll make a huge difference in which approach works best for u.

4

MERGE is perfect for this. It handles all the conditional logic internally and beats separate IF EXISTS checks every time. Here’s how to do it:

MERGE UserData AS target
USING (VALUES (@ID, @name, @email, @status)) AS source (ID, name, email, status)
ON target.ID = source.ID
WHEN MATCHED THEN
    UPDATE SET name = source.name, email = source.email, status = source.status
WHEN NOT MATCHED THEN
    INSERT (ID, name, email, status) VALUES (source.ID, source.name, source.email, source.status);

MERGE is atomic and cuts down database round trips. For high-frequency stuff, you’ll get less locking overhead and more consistent performance. Plus it handles concurrency way better than manual IF EXISTS checks, which can run into race conditions when multiple users hit it.