Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: SET IDENTITY_INSERT: Devil in the details...

    Need to ask this, 'cause as ya'all know I'm a GUID sort of guy with a sever IDENTITY crisis.

    Got a client that erroneously deleted some records from a table with an IDENTITY key. We have a backup that we can restore to a new db to isolate the records that need to be reloaded. Question is, when we set IDENTITY INSERT to ON temporarily to reload the affected records, does the db need to be in single-user mode, or will it go merrily on creating IDENTITY values for new records if the db is in use during our fix? It is a 24/7 production db with web users around the country/world.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    IDENTITY_INSERT is set at the spid level. You can mix and match to your heart's content, and while you can explicitly code to make conflicts possible (by binding sessions), there will be none that happen by accident.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks Pat. Did some testing myself. DB does NOT need to be in single-user mode, but while IDENTITY INSERT is on, all inserted records require specific identity value, so application transactions will fail. I guess we gotta see how many records need to be inserted to find out if we are dealing with a half-second script to do this, or a minute long complex process.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A curious thing that I found in one fix of a problem is that you can only have one table set to identity_insert at a time. The second will "set identity_insert __ on" statement will fail, until you have the original table set back to identity_insert __ off.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, that is in the documentation.

    But Pat pointed out to me that the IDENTITY_INSERT status is spid-specific, so I was able to run my identity inserts under my own login without having to worry about preventing other connections from running their normal inserts.

    Sweet.

    Thanks, Pat.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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