Best practice question

I'm very sorry if this has been covered before but I can't seem to find and answer with the way I've been searching.

The question is what would be concidered best practice when I have a row of data that I need to add or update but I don't know if the row exists yet. Would it be best to call SELECT for the record and INSERT if no data is found and UPDATE if the record is found, or to attempt the INSERT and then attempt an UPDATE if the insert fails?

The reference to a discussion over the past two days was not relevant. I suppose that I would first try the update; if the record is not present try the insert. In general my preference is to have the application intelligent enough to know what it is doing and have the stored procedure fulfill that specific function -- update, insert, select, delete, list or whatever.

I would urge you to avoid having the DELETE function be in the same stored procedure as the others.

|||I think you have to link this question with your real case. Let me explain: suppose you have to pay a bill, the person that get your money have to find your name or ID to see if you paid or not before. So , i think the SELECT preceed INSERT or UPDATE because this process have to be done by a person.
Another problem is that an computer operator insert a wrong or duplicate product, for that exist triggers, constraints...|||

Code Snippet

IF EXISTS (SELECT Statement)

BEGIN

-- It is there to be modified

END

ELSE

BEGIN

-- It is not there to be modified, can be added

END

|||

OK the way it will probably work is I have a table on another system that will have columns that hold total information for each hour of the day. The idea is to keep the two systems as close to concurrent as possible and so I'll be periodically using ODBC to update the SQL Server table on another system. Most often, the record keyed on a type, name, and date will be there so I guess trying the update first will succeed more often that fail so I was thinking of doing that first but then was wondering if it is more acceptable to first try to insert a new record and change to an update if that failed. There will be times with other tables that data will need to be synchronized where I will not be sure if the data already exists or not. This will also be for third party data warehousing of some information so I'm not sure if all DBA's are all that keen on adding stored procedures as aposed to creating tables separate from their normal data.

|||

OK one PITA thing about this is that I am using ODBC to make the client side SQL queries and the return from the UPDATE is the same when the record does or does not exist. I guess I'll have to either change to a stored procedure or live with errors from attempting an INSERT either before or after the UPDATE. I Think adding a SELECT will do little to enhance performance because it will at a minimum be the same number of statements.

|||

You need to perform the transactions under serializable isolation level for concurrent calls to work. Otherwise none of the methods suggested in the other posts will work. See the thread below for more details on how to implement it using locking hints:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384227&SiteID=1