April 8, 2008 at 1:07 pm
I just made a copy of my development database. I have removed all dummy data. Can someone tell me how to reset the identity specification on my tables back to 1? Whenever I insert a new record, it continues where it left off.
Thanks!
David
April 8, 2008 at 1:14 pm
Two rather straight forward ways. If the table has no foreign keys, and shouldn't have any data in it as of now, then TRUNCATE TABLE mytable would reset your identity.
The second way is to DBCC CHECKIDENT. It will allow you to reset a identity to use the next higher integer based on what is already in the table. You'd usually need to run two commands for that:
DBCC CHECKIDENT ('mytable', RESEED,0) --sets the next ID to 0
DBCC checkident ('mytable', RESEED) -- would adjust the ID to be the next int higher than the max still in the table
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 1:30 pm
Cool. Will Truncate Table Drop my foreign keys?
April 8, 2008 at 4:39 pm
No. In fact, you may find that you can't truncate a table that's being referenced by foreign keys.
Greg
April 8, 2008 at 5:23 pm
Greg Charles (4/8/2008)
No. In fact, you may find that you can't truncate a table that's being referenced by foreign keys.
Correct - I didn't specify that very well earlier. Greg is correct - Truncate will just issue a warning/error message, and refuse to run.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply