| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2004-08-10 : 11:17:11
|
| I have a few auto-increment columns serving as a Primary Key. Whilst testing the database, I inserted a number of records as a means of testing the system. The system has now gone live and I deleted all the test data. However, I want to reset the auto-increment columns again. Does anyone know how to do this?Hearty head pats |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-10 : 11:24:38
|
| Have a look at DBCC CHECKIDENT in BOL |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-10 : 12:45:57
|
| DBCC CHECKIDENT('TableName', RESEED, startValue)Go with the flow & have fun! Else fight the flow :) |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-08-11 : 03:53:48
|
| Thanks guys, much appreciated!Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-08-11 : 04:34:04
|
| Ermmmm, just been thinking. What effect will this have on the other tables that contain this value as an FK? Will they be changed also if I set the tables to auto cascade on update?ThanksHearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-08-11 : 05:07:03
|
| Hello, yes, its me.....again........The command: DBCC CHECKIDENT ([table name], RESEED, 1) does not work on the table because it contains values. Is there a method to reset the identity column if the tables already contain data? TaHearty head pats |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-11 : 05:09:25
|
| "Whilst testing the database, I inserted a number of records as a means of testing the system. The system has now gone live and I deleted all the test data.""other tables that contain this value as an FK?"There is a clash between these 2 statements....IF you deleted ALL test data...then the latter statement must be false. If you didn't delete ALL test data, then you have orphen records in your database....and you don't have any formal relationships/constraints between your tables in which case you have more DB admin work to do. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-11 : 05:15:37
|
| you could set the column to be non incremental, update it with sequential values and then set it to be incremetal again...and andrew is right. if the system is live, the tables should all be empty. what's the point in keeping test data???Go with the flow & have fun! Else fight the flow :) |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-08-11 : 05:38:26
|
| You are both absolutely right! I just did not make myself clear! There is absolutely no point in keeping test data, especially as it will produce incorrect results when querying the system.Unfortunately, however, the system was put live before asking me to delete the test data. As soon as the system went live, data was being entered into the DB. I was then given the task of deleting the test data, yet retaining the live data. Therefore, that is what I meant when I said that the identity column is an FK relating to other tables, as they now contain live data. Having deleted the test data, I now have identity values all over the place. To make things more complicated, we are doing another test run (whilst live) and I will have to delete this data from the system too! Not very efficient I know to be performing tests after launch!Anyway, I don't really want to make changes to the DB structure, as I do not want to lock the DB whilst live and lose important data! Is there no way to reset the identity values without playing with the DB itself? To be honest, there is no reason to do this apart from wanting to keep the system tidy. I can leave it as it is, but it wouldbe interesting to know if what I want to do is at all possible?is forHearty head pats |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-11 : 05:49:51
|
| well if you know what the test data is you can simply delete it and leave the identity field alone. why do you want to reseed it in the first place. it's not like you're going to run out of numbers.... :)if you really must do it, then you are in one hell of a work day...Go with the flow & have fun! Else fight the flow :) |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-08-11 : 06:01:26
|
Hmmmmmm, in that case I think I will leave well alone!!!!! I think my problem is that I am a perfectionist, which can incur extra workload. I was hoping for a simple solution, but as there is not one, well, I think I best get on with my other work!Thanks for all the advice peeps, much appreciated for your input!  Hearty head pats |
|
|
|
|
Resetting an Auto-Increment Column