Merge Statement

Question

Post reply

Merge Statement

Ells

SSCrazy Eights

Points: 9901
More actions
August 14, 2013 at 3:53 am

#296984

Have a nice lovely Merge statement updating the target table from the source.
We capture the updates with a WHEN MATCHED
We capture the insertes with a NOT MATCHED by Target
Its the pesky deletes.
We may wish to not apply the deletes (just dont ask :exclamation: however we may change our mind again)
However we would like to capture the number of potential deletes. Is this possible within the Merge?? I dont think it is.
When we panned to do the deleteions we used the output to capture the number iof rows updated, deleted, inserted etc.
Many Thanks
E

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

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

thava SSCrazy Points: 2275 More actions · Answer 1

Merge in msdn just read it i think you get a clear vision

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Ells SSCrazy Eights Points: 9901 More actions · Answer 2

Many thanks but I read that George posting the question. I can't see a way to get the number of potential deletion a unless I execute the delete command and scrape the output for the numb of deletions. Unless I am missing something?

Cheers

E

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

You could run the MERGE with WHEN NOT MATCHED SOURCE DELETE inside a transaction that you ROLLBACK, capturing the $ACTION from OUTPUT in a TABLE VARIABLE (so its contents are not rolled back).

You can then rerun the MERGE in a second transaction with COMMIT and without the WHEN NOT MATCHED SOURCE DELETE. Retrieve the data from the first OUTPUT statement and count the deletes.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Ells SSCrazy Eights Points: 9901 More actions · Answer 4

Thanks,

I was hoping miraculously that I had missed some extra functionality within the emerge statement.

Two separate transactions it is then.

Cheers

E

HowardW One Orange Chip Points: 29109 More actions · Answer 5

I'd avoid delete and rollback, it's needlessly expensive. If you don't need the delete, just put the equivalent logic in a SELECT statement to capture the count.

Ells SSCrazy Eights Points: 9901 More actions · Answer 6

Thanks.

I have not decided what to do yet. As there will be a significant overhead to doing the count we will need to consider if the information gathered is who the overhead of getting it.

If it takes 10 minutes to tell you that there would have been 10000 deflections, that could have happened but did not is it worth it. That's my question to ask the application owner and not my answer to give.

Many thanks

E