Video : The MERGE Statement

After a what seems like an eternity of being ill and having a dodgy throat, followed quickly by a couple of conferences, I’ve finally got back on the horse and recorded another video.

I was explaining a specific aspect of the MERGE statement to one of my colleagues and while I was doing it I was thinking, “Have I done a video on MERGE yet?” Now I have.

The cameo for this video is Cary Millsap. If you watch the out-takes at the end you will see the level of respect and trust I have garnered in the community. The words confused and suspicious spring to mind! 🙂

An honourable mention goes out to James Morle for videobombing. 🙂

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

2 thoughts on “Video : The MERGE Statement”

  1. Thanks Tim for this. I have had an issue with MERGE before. I would like to know if it is possible to control which (insert or update) executes first in a MERGE. I have explained my problem below. I finally got around this by replacing MERGE with UPDATE & INSERT separately.

    I want to maintain an audit table to track changes happening in the source table. Any update in source table, will end date the existing row in the target and create a new row for it. Any deletes in source table will be a soft delete in the audit table. The most recent (or active) rows in the target will have the same future date (example: valid_to_date = ’31-DEC-9999′).

    When there is an update at source, there will be an insert and update in the target. The valid_to_date is part of the key. The update & insert is done with a merge statement. I want the update (to end date the row) to run first followed by the insert. If the insert runs first there will be a unique key violation.

  2. Mukundhan: It doesn’t sound like merge is the right thing for this requirement. First, the merge is not going to detect deletes in the source table, so it can’t do the update in the audit table for you. Second, it sounds like you need two actions for every source row change (and insert and update), which is not what merge does. Neither of these points are what merge is designed for, so I’m not surprised you’ve “had an issue with MERGE”. 🙂

    Use the right tool for the job. 🙂

    Cheers

    Tim…

Comments are closed.