Geeks With Blogs

News
(Amazon Affiliate Ads)

Mike Parks Code.Where(c => c.IsTechnicalDebt).Refactor();

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

Posted on Saturday, June 18, 2011 1:46 AM | Back to top


Comments on this post: A little SQL tip for C# developers

# re: A little SQL tip for C# developers
Requesting Gravatar...
This is hand typed so it might have a typo or 2, but you can also use MERGE to do UPDATE/INSERT statements in SQL Server 2008.

MERGE INTO Table1 T
USING (SELECT 'SomeValue' AS Column1) S
ON T.Column1 = S.Column1
WHEN MATCHED THEN
UPDATE SET Column1 = S.Column1
WHEN NOT MATCHED
THEN INSERT VALUES (Column1);

More information here: http://msdn.microsoft.com/en-us/library/bb522522.aspx
Left by Zachary Hunter on Jun 29, 2011 2:33 AM

Your comment:
 (will show your gravatar)


Copyright © MikeParks | Powered by: GeeksWithBlogs.net