Post reply

Merge Statement

  • 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

  • August 14, 2013 at 4:57 am

    #1641299

    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]

  • August 14, 2013 at 5:10 am

    #1641315

    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

  • August 14, 2013 at 6:44 pm

    #1641622

    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

  • August 15, 2013 at 1:09 am

    #1641662

    Thanks,

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

    Two separate transactions it is then.

    Cheers

    E

  • August 15, 2013 at 1:47 am

    #1641668

    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.

  • August 15, 2013 at 1:52 am

    #1641671

    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

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

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