Home / server / Questions / 178370
Accepted
Matt Dawdy
Asked: 2010-09-06 19:32:23 +0800 CST

Change next auto-number assignment without recreating entire table, MS SQL Server 2000

  • 772

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?

sql-server

2 Answers

  1. Best Answer

    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).

    • 5

  2. 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;
    • 1