CODEWITHSUNDEEP
sqlsql-serverssms
CorgiDev
CorgiDev

Reputation: 93

SQL Server : alter the Identity seed

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?

Upvotes: 8

Views: 9838

Answers (2)

Jack
Jack

Reputation: 492

What I would do unset the new column as an identity (using alter table), then insert the data from the old table, and then reset the new column as the identity again, with whatever increment you want as per the link

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

Upvotes: 0

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

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

Upvotes: 12

Related Questions