Wednesday, August 01, 2007

Upsert Properly

{

For the longest time, my approach to update/insert logic has been the following:

IF EXISTS(SELECT...) BEGIN
UPDATE...
END
ELSE BEGIN
INSERT...
END

So courtesy of .NET Kicks this gem was very informative which is the same logic but with a cleaner approach. Instead of selecting records first, you attempt to do an update and check afterwards if the @@rowcount is greater than zero - if not the record doesn't exist so you can move logically to an insert.

UPDATE ...

IF @@rowcount = 0 BEGIN
INSERT...
END

Nifty, very nifty.

}

No comments: