SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
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 Previous Topic Topic Next Topic  

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/07/2013 :  12:49:52  Show Profile  Reply with Quote
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

4364 Posts

Posted - 02/07/2013 :  12:54:37  Show Profile  Reply with Quote
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

USA
228 Posts

Posted - 02/07/2013 :  13:05:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000