Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Reset an Identity Field on Existing Data

Author  Topic 

sross81
Posting Yak Master

228 Posts

Posted - 2013-02-07 : 12:49:52
I have a table that I have pulled in from an Access database that was corrupted and it kept allowing an auto number to repeat itself. The number is not used as a primary key, but it does need to remain unique. I want to just reset the reseed the whole field in the table, but I can't seem to find out how to do it with existing data only with new data.

I have tried

DBCC CHECKIDENT (Providers, RESEED,1)

It does nothing for me.

Any ideas? Is this even possible?

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-07 : 12:54:37
Reseeding the table is not going to update the Identity values, if that's what you are tryign to do. Depending on what you are trying to do, it might be easy to drop the existing column and add a new one that is an IDENTITY. If that won't work for you then I'd suggest you follow the link below and provide sample date and expected output:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts

Posted - 2013-02-07 : 13:05:12
I can just drop the identity field and create a new one that is what I always do to fix it in access. I was just wondering if there was a better way to do it in sql. Thank you.

Thanks in Advance!
Sherri
Go to Top of Page
   

- Advertisement -