Ben Hyrman

Basic MERGE with SQL Server

SQL Server 2008 introduced Merge which lets you do inserts, updates, and deletes on a target table based on a join to a source entity. While there are some pitfalls to be aware of, it can be a nice alternative to the 'if not exists insert else update' code we'd otherwise write.

IF EXISTS(SELECT 1 FROM RatePlans WITH (updlock, rowlock, holdlock) WHERE BusinessId = @BusinessId and RatePlanId = @RatePlanId)
UPDATE RatePlans SET RatePlanName = @RatePlanName WHERE BusinessId = @BusinessId and RatePlanId = @RatePlanId
ELSE
INSERT INTO RatePlans(RatePlanId, BusinessId, RatePlanName) VALUES(@RatePlanId,@BusinessId,@RatePlanName)

Becomes

MERGE into RatePlans WITH (updlock)
USING ( VALUES (@BusinessId, @RatePlanId, @RatePlanName)) as source(BusinessId, RatePlanId, RatePlanName)
ON source.RatePlanId = RatePlans.RatePlanId
AND source.BusinessId = RatePlans.BusinessId
WHEN MATCHED THEN
UPDATE SET RatePlanName = source.RatePlanName
WHEN NOT MATCHED THEN
INSERT (BusinessId, RatePlanId, RatePlanName)
VALUES (source.BusinessId, source.RatePlanId, source.RatePlanName);

This shows an upsert using parameterized values. While the Merge version doesn't necessarily read nicer, it can provide some advantage. At the least, it's a good option to have in your back pocket.