Problem

You need to insert new records and update existing ones from a staging table. The MERGE statement can introduce unexpected behaviour and locking issues in some environments.

Solution

Split the operation into an UPDATE followed by an INSERT.

-- Update existing rows
UPDATE t
SET t.some_column = s.some_column
FROM target_table t
JOIN staging_table s ON s.id = t.id;

-- Insert new rows
INSERT INTO target_table (id, some_column)
SELECT s.id, s.some_column
FROM staging_table s
LEFT JOIN target_table t ON t.id = s.id
WHERE t.id IS NULL;

Notes

• Easier to reason about than MERGE.
• Avoids known MERGE edge cases in SQL Server.
• Allows different validation logic for updates vs inserts.
• Often performs better for large batches.