A twist on the Merge statement

Question

A twist on the Merge statement

Atat Rowcount

Default port

Points: 1453
More actions
October 31, 2010 at 5:59 am

#225734

Hi
I often have the requirement to take data in from XML or CSV files and merge it with existing table data. I can't imagine that my requirement is rare or unusual.
However one thing I constantly find myself hit with is the scenario whereby data for an entity needs to be merged, the data does not contain all fields, and I obviously need to just update those which are provided.
For example...
MergeCUSTOMER c
Using(SelectCUST_REF,CUST_NAME,GENERAL_EMAIL,PO_REQUIRED
From@EXTERNAL_CUSTOMER) ec
Onc.CUST_REF = ec.CUST_REF
When Matched Then
Update SetCUST_EXTERNAL_KEY = ec.CUST_EXTERNAL_KEY,
CUST_NAME = ec.CUST_NAME,
EMAIL = ec.GENERAL_EMAIL,
PO_REQUIRED = ec.PO_REQUIRED
When Not Matched Then...
This is abbreviated from a merge containing 40+ fields, and all will not always be provided. Now....let's say "General Email" is not provided in one of my data inputs, I want to leave it as it is, and *not* over-write it with Null.
Traditionally I have done this by going....
Set EMAIL = coalesce(ec.GENERAL_EMAIL,EMAIL)
...which works, but is not ideal.
I'm only a few months using 2008 having come off 15 years of 2000/7.0/6.5/Sybase etc, so Merge is still a bit alien, and I'm hoping that there is a way in Merge that this can be done without me having to resort to coalesce and potentially have a serious hit on performance if I am hitting thousands of rows with my statement.
Wishful thinking? Or is there a 'new' way?!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Looking at the MERGE statement .. be aware that you can use two(2) statements in the WHEN MATCHED action, the gotcha is that each statement must contain an "AND'. From BOL

WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0

THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty

WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Atat Rowcount Default port Points: 1453 More actions · Answer 2

Hmmm..... that's an interesting idea. I may try it, but wouldn't that be likely to take a lot longer to run?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

If the source table is more than a few hundred rows, I'd use a temporary table instead of a table variable. You can index a table variable

DECLARE @EXTERNAL_CUSTOMER TABLE(<<columns>>, UNIQUE(CUST_REF)) but imho performance won't be as good as a #table.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

Brian,

The easiest way to do this is always update everything and use ISNULL() function to set the column to itself if the corresponding column is null. I use ISNULL rather than COALESCE since it's a tad faster. Try something like:

Update Set CUST_EXTERNAL_KEY = ISNULL(ec.CUST_EXTERNAL_KEY, CUST_EXTERNAL_KEY)

, CUST_NAME = ISNULL(ec.CUST_NAME, CUST_NAME)

, EMAIL = ISNULL(ec.GENERAL_EMAIL, EMAIL)

, PO_REQUIRED = ISNULL(ec.PO_REQUIRED, PO_REQUIRED)

I haven't yet tried this inside a MERGE statement, but it ought to work

Todd Fifield

Atat Rowcount Default port Points: 1453 More actions · Answer 5

Many thanks. That's pretty much what I've always done, except using IsNull instead of Coalesce (is it really faster?)

Was hoping there was a new way to do it 🙂

Thanks again

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Brian McGee-355525 (11/1/2010)
Many thanks. That's pretty much what I've always done, except using IsNull instead of Coalesce (is it really faster?)

There's been a number of timing tests out there and a few minutes with google will help you find them. The end result is yes, over a million+ rows I believe the difference was about 10% faster, with different limitations (ie: how the datatype is determined).

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA