SQL Server : alter the Identity seed

Question

I am migrating data from one database to another. I have my scripts mostly together already, but I am trying to figure out the best way to make one change to a table in the new database.

I have a Customer table. That table has a customer_id column which is the identity column. I want to change the identity seed/increment from (1,1) to (200,1) without changing the customer_ids for the existing data I will be inserting into the table.

Old data is 101-108. Basically we want to keep the old data the same so it matches up with old records in other systems, but we want the new data to start seeding in at 200.

I tried Googling how to do this, but all my Googling came back with results where people wanted to change what column was the identity column, and not just change the identity seed number. Is there a simple query I can use to accomplish what I want to do?

Answer 1

You can use DBCC CHECKIDENT:

DBCC CHECKIDENT ('dbo.customer', RESEED, 200)

This will change the current seed value of the identity column of the specified table. If you need to insert specific identity values, you can SET IDENTITY_INSERT ON in your insert statement.

IDENTITY_INSERT