MERGE

  • Comments posted to this topic are about the item MERGE

  • This was removed by the editor as SPAM

  • Nice question. Didn't know triggers had such an impact on MERGE.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (2/15/2012)


    Nice question. Didn't know triggers had such an impact on MERGE.

    +1

    I didn't really get the point you were making until I built the tables and the insert trigger, then everything became clear.

    Thanks for highlighting this.

    Ian

  • Bugger! Got bored Googling the answer (never done MERGE or INSTEAD OF Triggers before) and thought I'd found it so went for Yes. Only after being told I was wrong did I read the paragraph above the one I took my answer from...

    http://technet.microsoft.com/en-us/library/bb510625.aspx

    If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

    Boo hoo! ...as Cher would say... if I could turn back time! 😛

    Great question though, I learned something new yet again. All good stuff.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Koen Verbeeck (2/15/2012)


    Nice question. Didn't know triggers had such an impact on MERGE.

    +1

    Good question!!!

    thanks!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Great question! As with most people didn't think of all the limitations and was very annoyed when I got it wrong - with myself.

  • The BOL reference also says:

    The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

    I didn't see that semicolon in the statement in the QOTD.

  • Good question! What a bizarre restriction.

  • cengland0 (2/15/2012)


    The BOL reference also says:

    The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

    I didn't see that semicolon in the statement in the QOTD.

    I got it right, for all the wrong reasons.

    The script will not run, regardless of triggers, for two reasons:

    1. The MERGE statements must be terminated with a semicolon. This one isn't, so the script would fail.

    2. Adding column names to a table alias is not valid syntax. SQL Server will interpret this as a table hint - and neither Form_ID nor Form_Name is recognised as a valid hint.

    Without those two errors, I would probably have given the wrong answer. Thanks for teaching me something I didn't know!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Suprised at the results

    Correct answers: 34% (81)

    Incorrect answers: 66% (155)

    Total attempts: 236

    Thought many more would be correct, since BOL clearly states

    If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

    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]

  • Missed another point, but learned something today. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • bitbucket-25253 (2/15/2012)


    Suprised at the results

    Correct answers: 34% (81)

    Incorrect answers: 66% (155)

    Total attempts: 236

    Thought many more would be correct, since BOL clearly states

    If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

    At 9:04 AM Eastern Standard Time

    [Center]Correct answers: 33% (104)

    Incorrect answers: 67% (208)

    Total attempts: 312 [/Center]

    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]

  • cengland0 (2/15/2012)


    The BOL reference also says:

    The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

    I didn't see that semicolon in the statement in the QOTD.

    Indeed! This makes the answer "no" even more right! 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Good question.

    I don't think I have ever written an instead of trigger. I had to wrestle with them early in my career (didn't know how to disable a trigger at that point) and they were nothing but a pain. I still don't quite get the point to be honest. It is kind of like saying "you can insert data into this table but this trigger is going to do something different anyway". I can sort of conceptually see where they could be useful but it seems like such a rare occurrence.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 35 total)

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