A little SQL tip for C# developers
- by MikeParks
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
