Rewriting the code using merge statement

Question

Post reply

Rewriting the code using merge statement

koti.raavi

SSCrazy

Points: 2756
More actions
September 8, 2016 at 11:59 pm

#312860

I have code below and trying to rewrite the code using merge statement , but i'm pretty new to use merge any help greatly appreciate

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

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

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

There's no advantage to rewriting such a thing as a MERGE. I wouldn't waste my time doing such a thing.

If you want to learn MERGE, start on something simple.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Any particular reason? Merge is a pain to write, no more efficient than separate statements in most cases, and has been found to have assorted bugs in it over the years.

Personally I rather avoid merge.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

GilaMonster (9/9/2016)
...
Personally I rather avoid merge.

Really? I now feel better about avoiding merge too. 🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

koti.raavi SSCrazy Points: 2756 More actions · Answer 4

I feel like merge will reduce the code length and improve the performace..if that is the case i will avoid merge..or is there any we can rewrite above code for better performance..thanks

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 5

Alvin Ramard (9/9/2016)
GilaMonster (9/9/2016)
...
Personally I rather avoid merge.
Really? I now feel better about avoiding merge too. 🙂

Makes three of us.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 6

koti.raavi (9/9/2016)
I feel like merge will reduce the code length and improve the performace..if that is the case i will avoid merge..or is there any we can rewrite above code for better performance..thanks

It looks like you've got 4 statements there, creating a temp table, two inserts then a delete. If you run them 1 by 1 instead of as a batch is there any particular one that runs slow?

drew.allen SSC Guru Points: 76945 More actions · Answer 7

A MERGE won't work here. MERGE requires all of the actions be performed on the same table. Here you are inserting into one table (tbl_OOA_Source_Exception), but deleting from another (tbl_OOA_Source_PreScrub).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA