SQL Server UPSERT: Insert or Update a Row

An UPSERT inserts a row when its key does not exist and updates the row when that key already exists. In SQL Server, you can implement this with an UPDATE followed by a conditional INSERT, or with MERGE. For a single key in an application request, the two-statement pattern below keeps both decisions in one transaction and protects the missing-key check with locks.

Create a table with a unique key

The column used to find the row must be unique. This example uses Sku as the primary key:

CREATE TABLE dbo.Inventory (
  Sku varchar(30) NOT NULL
    CONSTRAINT PK_Inventory PRIMARY KEY,
  Quantity int NOT NULL
);

The unique key is the final guarantee that two rows cannot be stored for the same SKU. A lock hint does not replace this constraint.

Insert a row or update the existing row

Run the update and possible insert in the same transaction. The UPDLOCK and SERIALIZABLE hints protect the key lookup until the transaction ends, including the key range when no matching row exists.

DECLARE @Sku varchar(30) = 'BK-15';
DECLARE @Quantity int = 5;

SET XACT_ABORT ON;

BEGIN TRY
  BEGIN TRANSACTION;

  UPDATE dbo.Inventory WITH (UPDLOCK, SERIALIZABLE)
  SET Quantity = @Quantity
  WHERE Sku = @Sku;

  IF @@ROWCOUNT = 0
  BEGIN
    INSERT INTO dbo.Inventory (Sku, Quantity)
    VALUES (@Sku, @Quantity);
  END;

  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

  THROW;
END CATCH;

On the first run, SQL Server inserts BK-15. Run the batch again and it updates the existing quantity to 5. Replace the declared values with parameters in a stored procedure or a parameterized application query.

@@ROWCOUNT is checked immediately after the UPDATE: zero means no row matched, so the batch inserts one. Keep the equality predicate on the unique key. Without a useful index, SQL Server may scan more rows and hold locks longer.

With SET XACT_ABORT ON, a run-time statement error terminates and rolls back the transaction; compile errors aren’t affected. The CATCH block rolls back any transaction that remains active or uncommittable, then THROW returns the error to the caller. This error-handling example uses THROW, available in SQL Server 2012 and later. See Microsoft’s SET XACT_ABORT, TRY...CATCH, and XACT_STATE references.

Use MERGE for a source row or set

MERGE can insert a row when the source key is missing and update it when the key matches:

MERGE INTO dbo.Inventory WITH (HOLDLOCK) AS target
USING (VALUES
  ('BK-15', 5),
  ('PN-02', 4)
) AS source (Sku, Quantity)
  ON target.Sku = source.Sku
WHEN MATCHED THEN
  UPDATE SET Quantity = source.Quantity
WHEN NOT MATCHED BY TARGET THEN
  INSERT (Sku, Quantity)
  VALUES (source.Sku, source.Quantity);

The statement must end with a semicolon. In the ON clause, compare only the columns that identify a matching row. Put additional filters in a WHEN condition; Microsoft’s MERGE reference warns that filtering the target in ON can produce incorrect results.

HOLDLOCK uses serializable semantics and can prevent a concurrent insert from racing with a MERGE for a unique key. However, Microsoft notes that MERGE can introduce concurrency complexity and that separate statements may block less under heavy concurrency. Test the statement with the real indexes, triggers, and workload before using it in production. For a simple application-level upsert, start with the transaction pattern above; use MERGE when its set-based form is useful and you have validated its concurrency behavior.

Avoid common UPSERT mistakes

• Checking then inserting without a transaction: another session can insert the same key between the check and your insert. Keep the lookup and write in one transaction, and retain the unique constraint.
• Using a non-unique match key: make the key unique in the target table. For MERGE, duplicate source rows that match one target row can make SQL Server return an error instead of updating the same target repeatedly.
• Adding status filters to MERGE ON: match on the business key in ON; use WHEN MATCHED AND ... or WHEN NOT MATCHED AND ... for action-specific conditions.
• Adding lock hints without measuring: SERIALIZABLE range locks reduce concurrency. Azure SQL Database and SQL Server 2025 databases with optimized locking enabled honor hints such as UPDLOCK and HOLDLOCK, but Microsoft notes that these hints can reduce optimized locking’s benefits. Use hints where the race requires them and test under expected concurrency.

For the equivalent syntax in other databases, see MySQL ON DUPLICATE KEY UPDATE, PostgreSQL INSERT ON CONFLICT, and SQLite UPSERT.

For a side-by-side comparison that includes SQL Server, see SQL UPSERT Syntax by Database.