MERGE

  • Learnt something new, though I answered wrongly...

  • Hugo Kornelis (2/15/2012)

    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!

    And learnt even more...

  • SathishK (2/15/2012)


    Hugo Kornelis (2/15/2012)

    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!

    And learnt even more...

    Go back and look at the QOD - Steve added the ;

    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]

  • Good question!!

    Thanks

  • great question and good lesson for me on an area i am less familiar with - tks

  • Sean Lange (2/15/2012)


    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.

    One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.

    Another good example for instead of triggers (but instead of delete, not instead of insert) would be to transparently implement soft deletion.


    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/

  • bitbucket-25253 (2/15/2012)


    SathishK (2/15/2012)


    Hugo Kornelis (2/15/2012)

    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!

    And learnt even more...

    Go back and look at the QOD - Steve added the ;

    And removed the column list from the alias. Good job, Steve! 😉


    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/

  • Hugo Kornelis (2/15/2012)


    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!

    +1

    This script will not run because it will not get compiled.

  • Darn. Second guessed myself and got it wrong. I don't use triggers often but I have been using merge more and more recently. Good to know anyways.

  • Hugo Kornelis (2/15/2012)


    Sean Lange (2/15/2012)


    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.

    One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.

    Another good example for instead of triggers (but instead of delete, not instead of insert) would be to transparently implement soft deletion.

    Thanks Hugo. I had not thought of the insert that way.

    As for soft deletes, I like the example but I avoid soft deletes like the plague. 😛

    _______________________________________________________________

    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/

  • Good question (I was lucky and didn't see it until after the errors were fixed ;-).

    I didn't know the answer (I'm very familiar with instead of triggers but I'm not really familiar with merge) so I looked at the BoL page for merge and asked my browser to find "trigger" on that page - and that took me straight to it. So I learned something :-), but it was so easy to find that I may well forget it :(.

    Tom

  • Hugo Kornelis (2/15/2012)


    Sean Lange (2/15/2012)


    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.

    One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.

    An example of an instead of insert trigger on a table (as opposed to on a view) is a trigger that ensures that some uniqueness constraint is met by inserting only rows that won't cause it to be violated and discarding other rows - if we want to insert those rows and not let the duplicates (if any) make teh constrain cause the insert to fail, an instead of insert trigger is the only way to do it. This works nicely when recursive triggers are disabled. Maybe also when they are enabled.

    Of course for INSERT statements, a stored procedure containing much the same code as the trigger could be produced, and everything that wants to insert has to call thet proc instead - but I think this SP approach may not always be possible if MERGE statements are being used.

    Tom

  • I didn't know about this "feature." It's good to know.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • L' Eomot Inversé (2/15/2012)


    Hugo Kornelis (2/15/2012)


    Sean Lange (2/15/2012)


    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.

    One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.

    An example of an instead of insert trigger on a table (as opposed to on a view) is a trigger that ensures that some uniqueness constraint is met by inserting only rows that won't cause it to be violated and discarding other rows - if we want to insert those rows and not let the duplicates (if any) make teh constrain cause the insert to fail, an instead of insert trigger is the only way to do it. This works nicely when recursive triggers are disabled. Maybe also when they are enabled.

    Of course for INSERT statements, a stored procedure containing much the same code as the trigger could be produced, and everything that wants to insert has to call thet proc instead - but I think this SP approach may not always be possible if MERGE statements are being used.

    Thanks Tom. I suppose that would let you stick those offending records in an error table or something too.

    _______________________________________________________________

    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/

  • Great question. Always something new to learn.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

Viewing 15 posts - 16 through 30 (of 34 total)

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