SQL Server Forums - Reset an Identity Field on Existing Data

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reset an Identity Field on Existing Data

New Topic

Reply to Topic

Printer Friendly

Author

Topic

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/07/2013 : 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
Flowing Fount of Yak Knowledge

3856 Posts

Posted - 02/07/2013 : 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

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/07/2013 : 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

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.03 seconds.