What is the benfit of MERGE in SQL 2008?

Question

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)

Answer 1

0

You'd have to have a lot of logic in the WHEN MATCHED THEN UPDATE part of the statement. No less then you have now, probably more. Drop your data into a table variable (or better yet use a table input parameter) then do a merge between the table variable and the table.

WHEN MATCHED AND target.YourEditNumber <= source.YourEditNumber            
   THEN UPDATE SET target.Column1 = CASE WHEN source.Column1 IS NULL THEN             
        target.Column1             
   ELSE            
        CASE WHEN Source.Column1 = '' THEN NULL ELSE Source.Column1 END            
   END            
   ...            
WHEN MATCHED AND source.YourEditNumber IS NULL            
   THEN INSERT            
   ...            
WHEN MATCHED AND source.YourEditNumber = -1            
   THEN UPDATE {SomethingHere}...

answered Oct 07 '09 at 04:22 PM

mrdenny
897 ● 3

Thanks. I'm not familair with the syntax, so it seems foreign (obviously!) but I do wonder if it is harder [ie. more costly] to debug / maintain than what I currently have?. The UPDATE and the INSERT statements are still separated - so have to look in two places for how "Column1" is handled, for example

Oct 07 '09 at 05:05 PM Kristen ♦

It's probably harder to maintain at first. It's probably to run as only a single statement has to be run, instead of either checking if the record exists or doing the update first, then insert if no row (in normal cases when the client isn't sending that info in). Test and see. Sadly my code has to run on SQL 2005 and SQL 2008 so I can't use MERGE yet myself.

Oct 07 '09 at 05:13 PM mrdenny

(comments are locked)

Answer 2

0

One big benefit of MERGE is that it's a better alternative to the buggy, non-standard and ill-conceived "UPDATE ... FROM" syntax. MERGE can be used as a safer and better alternative to UPDATE ... FROM even when you only want to do UPDATEs. And it's generally quicker than the UPDATE!

Hopefully UPDATE ... FROM can now be deprecated and removed in some future version.

answered Oct 13 '09 at 11:31 AM

David 1
1.8k ● 1 ● 3

Interesting. What's "buggy" about "UPDATE FROM"?

Oct 16 '09 at 07:04 AM Kristen ♦

(comments are locked)

What is the benfit of MERGE in SQL 2008?

Follow this question

Related Questions