I think everyone already knows my opinions about
MERGEand why I stay away from it. But here’s another (anti-)pattern I see all over the place when people want to perform an upsert (update a row if it exists and insert it if it doesn’t):
IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key) BEGIN
UPDATE dbo.t SET val = @val WHERE [key] = @key;
INSERT dbo.t([key], val) VALUES(@key, @val);
This looks like a pretty logical flow that reflects how we think about this in real life:
Does a row already exist for this key?
– YES: OK, update that row.
– NO: OK, then add it.
Click through to learn why this is a bad idea.