The other day at work I came across a handy little block of SQL code from Jeremiah Clark's blog. It's pretty simple logic but through the mind of a C# developer making some quick DB updates, seems to me that it's more likely to end up writing out the code in Solution 1 instead of Solution 2 below to solve the problem.

Basically, I needed to check and see if a specific record existed in Table1. If it does exist, then update that record, otherwise insert a new record into Table1.


Solution 1:
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

Solution 2:
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)
   
   
As Jeremiah explains, they both accomplish the same thing but from a performance standpoint, Solution 2 is the better way to go (saved table/index scan). Just wanted to throw this small tip out there. Thanks!

- Mike