Which approach to concurrency safe upserts is best for table-valued input if MERGE is forbidden?

Problems with MERGE usually manifest with combinations of features (especially relatively new ones) that result in highly complex data changing plans. This is a shame because MERGE is undoubtedly a convenient way to express things like an 'upsert'.

Workaround

You could consider the following:

1. Create a view over the target table.
2. Create INSTEAD OF trigger(s) for view INSERT and UPDATE actions.
3. Write your 'upsert' as a MERGE against the view.

Advantages:

1. The MERGE sees a simple target, so the data changing plan is not complex.
2. SQL Server populates separate internal worktables for inserted and deleted*.
3. The trigger code processes simple inserts and updates separately.
4. You no longer need an explicit transaction around separate insert and update statements.

Disadvantages:

1. The triggers add some overhead.
2. You have to remember to target 'upserts' to the view.
3. The OUTPUT clause won't work for inserted references.

This workaround allows you to write a convenient MERGE, but have the resulting actions performed as separate simple inserts and updates.

The usual precautions for concurrency on the MERGE are still needed.

I have used a view assuming you prefer not to have INSTEAD OF triggers on the base table.

_{* INSTEAD OF triggers do not use row versioning. See my article, Interesting things about INSTEAD OF triggers.}

Demo

There are improvements to be made (like possibly combining the triggers). The following code prioritises clarity:

db<>fiddle

Table and sample rows

CREATE TABLE dbo.AccountDetails ( Email nvarchar(400) NOT NULL CONSTRAINT PK_AccountDetails PRIMARY KEY CLUSTERED, Created datetime NOT NULL DEFAULT GETUTCDATE(), Etc nvarchar(max) NULL ); INSERT dbo.AccountDetails (Email, Etc) VALUES (N'[email protected]', N'Original 2792'), (N'[email protected]', N'Original 3129'), (N'[email protected]', N'Original 4726'), (N'[email protected]', N'Original 5766'); 

View

CREATE VIEW dbo.AccountDetails_Upsert WITH SCHEMABINDING AS SELECT Email, Etc FROM dbo.AccountDetails; 

INSTEAD OF INSERT view trigger

CREATE TRIGGER AccountDetails_Upsert_InsteadOfInsert ON dbo.AccountDetails_Upsert INSTEAD OF INSERT AS IF ROWCOUNT_BIG() = 0 RETURN; SET NOCOUNT, XACT_ABORT ON; SET ROWCOUNT 0; IF NOT EXISTS (SELECT * FROM Inserted) RETURN; -- Simple insert INSERT dbo.AccountDetails (Email, Etc) SELECT Email, Etc FROM Inserted; 

INSTEAD OF UPDATE view trigger

CREATE TRIGGER AccountDetails_Upsert_InsteadOfUpdate ON dbo.AccountDetails_Upsert INSTEAD OF UPDATE AS IF ROWCOUNT_BIG() = 0 RETURN; SET NOCOUNT, XACT_ABORT ON; SET ROWCOUNT 0; IF NOT EXISTS (SELECT * FROM Inserted) RETURN; -- Simple update UPDATE AD SET Etc = I.Etc FROM Inserted AS I JOIN dbo.AccountDetails AS AD ON AD.Email = I.Email; 

Table-valued MERGE

DECLARE @Changes AS table ( Email nvarchar(400) NOT NULL PRIMARY KEY, Etc nvarchar(max) NULL ); INSERT @Changes (Email, Etc) VALUES -- Update some rows (N'[email protected]', N'Updated 2792'), (N'[email protected]', N'Updated 3129'), (N'[email protected]', N'Updated 4726'), (N'[email protected]', N'Updated 5766'), -- Insert some rows (N'[email protected]', N'Inserted a'), (N'[email protected]', N'Inserted b'); MERGE dbo.AccountDetails_Upsert WITH ( UPDLOCK, SERIALIZABLE, FORCESEEK ) AS V USING @Changes AS C ON C.Email = V.Email WHEN MATCHED THEN UPDATE SET Etc = C.Etc WHEN NOT MATCHED BY TARGET THEN INSERT (Email, Etc) VALUES (C.Email, C.Etc); 

Results

MERGE plan

Tidy up

DROP VIEW IF EXISTS dbo.AccountDetails_Upsert; DROP TABLE IF EXISTS dbo.AccountDetails;