Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I've got a table with a primary key that is an int, auto-number auto-increment. Your standard primary key type field.

Due to a complicated scenario with a previously replicated database, now I've got rows already in the table with values up to 410000 yet the value report from IDENT_CURRENT is only 400060 or so. Now, I've got code that is trying to insert records and blowing up because the database thinks that it should use number 400060, and yet I already have a record with that id. Kaboom.

I've looked into manually setting the next number in the table designer, but when viewing the script it generates, it is going to create a duplicate table, copy all records to it, drop the old table, rename it, do some stuff with constraints, etc. It's all very scary when I just need a simple change.

Is there a back way to change this value that won't cause so many unneeded changes?

share|improve this question
    
In case anyone in the future comes along here, I figured out that even though my inserts weren't working, it WAS incrementing the next number it would use. So, I manually tried to insert the right number of items (all failed) until I got to the right number. –  Matt Dawdy Sep 6 '10 at 3:41

2 Answers 2

up vote 3 down vote accepted

use the following on your table

dbcc checkident ('dbo.table',reseed,410000)

it changes the current identity value to 410000, so the next insert will be whatever the icrement is plus 410000 (so 410001 if the increment is 1).

share|improve this answer

What about something like the following that only removes the offending records, then reinserts them from a temp table?

SELECT * INTO #temptable FROM tablename WHERE ID_FLD>400059;

DELETE * FROM tablename WHERE ID_FLD>400059;

SELECT * INTO tablename FROM #temptable; 
share|improve this answer
    
Glad to hear you sorted it out. –  nedm Sep 6 '10 at 3:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.