In another question I asked ("What changes have you made to your SQL Code in SQL 2008") I received a couple of responses that MERGE was a particularly useful addition.
I'd be grateful for your thoughts on how it might impact what we currently do (I'm still stuck in the SQL 2000 world)
For our CRUD applications we have a "SAVE" Sproc that is designed to save a new record, or update an existing one. This Sproc is mechanically generated (although the validation it can perform, and sometimes other aspects, is created by humans!)
The premise of our SAVE Sproc is that is has a parameter for every available field. All fields are VARCHAR (or TEXT where necessary). Any parameter with a value (i.e. not NULL) means that the field was presented to the user [on a maintenance form] and thus an Empty String value in that field means the field should be cleared (we actually set the field to NULL). Conversely a parameter which is NULL, the default for all parameters, means that the field was not on the maintenance form, and thus has not been "presented" to the Sproc. As such we take "Blank string" to mean "Empty the field" and NULL to mean "Ignore the field".
This allows us to add columns to the table and any existing forms will not interfere with the new columns - the Form will not present those columns to the SAVE Sproc, and the Sproc will leave them unchanged
Additionally we have an "Edit Number" column in every table (along with Create Date/User and Update Date/User). The Edit Number is stored (in a hidden field) on the maintenance forms, and thus is passed to the SAVE Sproc. If the Edit Number does not match the current value then someone else has saved the record in the meantime, and the later user's save is disallowed - "pessimistic locking" (or is it "Optimistic locking", gosh I'm getting old!
(We have two special values of Edit Number: NULL = Forced Insert - the record must not already exist; -1 = The record MUST exist, but I don't know & don't care what Edit Number it has - useful if just updating the Status, say)
The Sproc contains a conditional IF based on the value of Edit Number. If >= 1 then perform an UPDATE, if NULL perform an insert. (If Edit Number = -1 then get the actual Edit Number, if a record exists, ahead of this test - the use of -1 is intentionally a rare, and carefully considered, event, thus not a performance burden)
All-in-all I consider our SAVE Sprocs to be "Upserts" as they can do either, hence the question about comparison with MERGE. However, our SAVE Sproc does not need to do an EXISTS test (unless given the "-1" Edit Number parameter)