Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: Updating identitycolumn using cursor/stored procedure

    There is a table with an identitycolumn containing several thousands of records. The identityvalue is referenced in 3 or 4 other tables.
    The values have increased dramaticly because of sloppy shutdowns and other causes. It isn't causing any problems yet, but before it does I want to fix it. I know how to repair the identitygap, but that only fixes the next number being used, not existing values.

    I tried to write a stored proc that uses a cursor on the basetable, taking each row, updating the identitycolumnsvalue (starting at 1) and updating it in the referencing tables. This mechanism returns a message saying that the column that is declared for update in the cursur is an identityproperty and thus is not updatable.
    'set identity_insert on' does not fix this error, and I don't know how to get it to work.
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Have you tried
    set identity_update table_name {on | off}

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    No, I didn't knew that was an option. I'll try it, thanks.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Unfortunately, set identity_update does not work. I keep getting the same error whn I execute the script.
    It seems that the only way to fix things is to copy all data to a shadowtalbe without the identityproperty, fix the values, empty the original table and replace the data, and fix the nextnum-value.
    Or not using a cursor, but I haven't yet figured out how to do that.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    This article appears to cover what you're after (especially the bit "A better way of repairing identity gaps"). Another method might be to leave the gaps alone and just fix the way they shut down the database

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by mike_bike_kite
    This article appears to cover what you're after (especially the bit "A better way of repairing identity gaps"). Another method might be to leave the gaps alone and just fix the way they shut down the database
    Thank you for the link. Unfortunately, I cannot change the way the application works or how the database is designed.

    The gaps do not cause any problems yet, there is room enough for the larger values, but I want to be ahead of any problems. I'll leave it for now, if I come up with a decent strategy I'll continue and post it here too.
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I don't think you need to change how the database is designed - you just create a translation table to move the old id's to the new ids. Populating this table might be the hard part but you could test your code by inserting a single record into it to alter the id of a test record. You'd then use a single update statement to alter all the ids in a given table then repeat this update on each table. Obviously you still need to be able to alter the identifier fields before doing any of this. You'd also have to be careful of triggers etc. The database will also be unusable while this process is happening but if you only have a few thousand records it shouldn't take to long. I'd have a backup in place just in case it all goes screwy

    Mike

    PS I'm not sure you can alter the identity fields in more than one table at a time so that be causing issues with the current method. I believe you need turn this option on for one table, do all your updates and then turn the option off before moving on to the next table. I could well be wrong here though.

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by mike_bike_kite
    I don't think you need to change how the database is designed - you just create a translation table to move the old id's to the new ids. Populating this table might be the hard part but you could test your code by inserting a single record into it to alter the id of a test record. You'd then use a single update statement to alter all the ids in a given table then repeat this update on each table. Obviously you still need to be able to alter the identifier fields before doing any of this. You'd also have to be careful of triggers etc. The database will also be unusable while this process is happening but if you only have a few thousand records it shouldn't take to long. I'd have a backup in place just in case it all goes screwy

    Mike

    PS I'm not sure you can alter the identity fields in more than one table at a time so that be causing issues with the current method. I believe you need turn this option on for one table, do all your updates and then turn the option off before moving on to the next table. I could well be wrong here though.
    I had not thought about a conversiontable, good point. I think it will work that way.
    I'm currently experimenting in a copydatabase, so I'm safe with that. Things will be tested thoutoughly before executed in production .

    Thanks!
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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