CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
jr3
jr3

Reputation: 915

How to reseed/replace identity columns on existing data

Hey all I want to reseed my IDENTITY COLUMN values starting from 1 I know how to do this with DBCC CHECKIDENT, however, I would like to replace the value in all existing rows.. This table has a little over 2 million rows.

What is the best approach for this task?

Upvotes: 5

Views: 5711

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21776

See this example how to replace values, but RESEED is something else:

CREATE TABLE t (id INT IDENTITY)
GO
INSERT t DEFAULT VALUES
GO 25

SET IDENTITY_INSERT t ON

delete t 

OUTPUT DELETED.Id+100 INTO T(Id)

SET IDENTITY_INSERT t Off

SELECT * FROM t

DROP TABLE t

An example of reseed:

DBCC CHECKIDENT('YourTableName', 150, reseed)

AND

If you have to replace the value - with 2M rows it definitely have to take time

Upvotes: 2

Chris Shain
Chris Shain

Reputation: 51344

You can simply add a new identity column, a la How to add a new identity column to a table in SQL Server?. Just delete the old column and re-add it. This will break any foreign keys, of course, but I assume since you are re-numbering everything I am guessing that's ok.

Upvotes: 6

Related Questions