x

What is the benfit of MERGE in SQL 2008?

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)

more ▼

asked Oct 07, 2009 at 03:09 PM in Default

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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}...
more ▼

answered Oct 07, 2009 at 04:22 PM

mrdenny gravatar image

mrdenny
908 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, 2009 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, 2009 at 05:13 PM mrdenny
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 13, 2009 at 11:31 AM

David 1 gravatar image

David 1
1.8k 1 3

Interesting. What's "buggy" about "UPDATE FROM"?
Oct 16, 2009 at 07:04 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x47

asked: Oct 07, 2009 at 03:09 PM

Seen: 1548 times

Last Updated: Oct 07, 2009 at 03:09 PM