Insert/Update on SQL Server 2005 without using Stored Procedures

Question

I'm trying to do the classic Insert/Update scenario where I need to update existing rows in a database or insert them if they are not there.

I've found a previous question on the subject, but it deals with stored procedures, which I'm not using. I'd like to just use plain SQL SELECT, INSERT and UPDATE statements, unless there's something better available (the MERGE statement isn't available in SQL Server 2005).

I guess my general idea is this:

If the row is found
  update
else
  insert

As for checking for a row's existence, how expensive is it to do a SELECT statement before calling an UPDATE or an INSERT? Or is it better to just try an UPDATE, check for the number of rows affected, and then do an INSERT if the rows affected is 0?

Answer 1

The most efficient way is to do the UPDATE, then do an INSERT if @@rowcount is zero, as explained in this previous answer.

Answer 2

(First of all - I would not try to avoid stored procedures, if there is no strong reason. The give a good benefit in most cases.)

You could do it this way:

• create a (temporary) table
• fill in your rows
• run a INTERSECT that identifies the extisting rows
• update your table with them
• run a EXCEPT that identifies the new rows
• run an insert with these elements
• drop/clear your (temporary) table

This will probably run faster, if you are inserting/updating large amounts of rows.

Answer 3

Completely understand that your post was titled "SQL Server 2005" but just wanted to throw out something to look forward to if/when you upgrade to SQL 2008. Microsoft has added a new MERGE statement which will give you the ability to code one DML statement that does both the update and insert. It's pretty cool. I've yet to compare performance and I/Os but it's just great to have another tool in your toolbox.