Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Insert or Update shortcut?

    I have a list of IDs and a Table. I want to update a date column in the table, where the primary key is in the list of IDs. Some of the rows may not be in the table yet, so its one of those EITHER insert OR update situations.

    Normally I would loop through each ID in the list, check if the row exists in the table, if it does, update it, otherwise insert a new row. But I realized that since its the primary key, i can just do a blind insert all the rows, followed by a blind update all the rows, with no checking for exsitance. SQL wont let me insert a duplicate primary key, so I get the correct behavoir of either inserting or updating depending on existance.

    The question is, is this good practice?? Is it ok to rely on the primary key uniqueness requirement like that? Or should I be doing the existance checks like a sucker. Also, I'm getting an ugly warning message "Violation of PRIMARY KEY constraint" when the rows already exist, so is there a way to supress that warning just for this script?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The MERGE statement in SQL 2008 might be just the thing for you.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    MERGE is perfect and a very, very powerful statement. If you are pre 2008 then you need to perform what is informally known as an UPSERT.

  4. #4
    Join Date
    Feb 2010
    Posts
    75
    Thanks very much!

    I had tried using MERGE a while ago but couldnt get it to work because of the strange syntax. After your suggestions I gave it another go and it seems to work. Pretty handy once you're used to the syntax.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Keep playing with it - there's lots of little goodies in there that you can use (e.g. variable assignment in the same statement as DML changes on a table).
    Sadly we only have one SQL 2008 in prod

  6. #6
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    I hadn't come across merge before until I saw this post a few days ago.

    So I started playing around with it - thinking it might help with some of our monthly third party data loads where we use a lot of if exists, update, if not exists, insert, if no longer exists delete syntax, sometimes slowly through cursors, sometimes through set based operations.

    So far I've got one of our processes that previously took about 15 minutes to run with a combination insert/update/delete down to just under a minute with a single merge statement - Brilliant! Particularly love the Output feature - very nice way of showing exactly what has changed instead of relying on timestamps etc.

    Now I just need to get the powers that be to approve upgrading our one remaining SQL 2005 server to 2008 so I put this into general distribution and implement it in a few more scenarios.

    If this post had of been 2 weeks later I might well have found myself looking a bit confused whilst sitting the upgrade MCITP 2005 db developer to 2008 exam thinking what the hell is this merge thing this question is talking about? Going to have to revisit the changes/new features list and make sure I haven't missed anything else so I can be sure I'm all caught up for next week.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •