Reset identity column for existing values without deleting existing records

Question

I have table having 100000 records. In this, I have one column which is Identity column. Now I want update all these records for Identity column only. I want to update all existing identity reseed and start from 3000.

I found this query but it only works for new records:

DBCC CHECKIDENT ('[Patient]', RESEED, 3000);

Why would you want to do that? Identity column values are never meant to be business values. — Raj, Apr 27 '16 at 05:40
You could check the following link. Might be helpful to you. http://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server — Nagahornbill, Apr 27 '16 at 05:47
I am facing this issue on Production server, and now want to fix this. Do you have any solution? — Parveen, Apr 27 '16 at 05:47
@Nagahornbill I have foregin key reference So I can't delete records — Parveen, Apr 27 '16 at 05:48
You could do some weird SWITCH TO with a temporary table then reseed the identity afterwards. Foreign key might complicate things, though. — ZLK, Apr 27 '16 at 05:51
I agree with Raj - I'd always suggest that if you care about the *numeric* value of an identity column, you're using it wrong. They should be used when you want a unique, opaque *blob* of data by which you can uniquely identify rows, and they *happen* to fit into columns with numeric types. — Damien_The_Unbeliever, Apr 27 '16 at 07:12

score 2 · Answer 1 · answered Apr 27 '16 at 07:46

I found the solution my self

step 1) go to SQL tools menu step 2) select Options menu step 3) New popup window will open, click on Designers step 4) Uncheck the checkbox - Prevent saving changes that required table re-creation step 5) right click on your table step 6) open in design mode step 7) delete Identity column step 8) Again add deleted column with Identity with below query

 alter table tableName
add columnName BIGINT identity(5000,1)

It will just work

score 0 · Answer 2 · answered Apr 27 '16 at 06:51

0

Dbcc checkident with reseed will change actual identity counter, not values in table. To change values you must use "set identity_insert"

answered Apr 27 '16 at 06:51

Piotr Lasota

201
1
6

score 0 · Answer 3 · edited Apr 27 '16 at 07:16

Here's another possible solution. Create a new table with the same definition as your Patient table (lets call it PatientNew). Reseed that table to the start of your required value 3000 - 1. Now copy the Patient table to the new table, it will then have identities according to your requirement. Delete the old table and rename the new one. Something like this:

  delete from PatientNew
  dbcc checkident('PatientNew', reseed, 2999)
  insert PatientNew(Col2, Col3) select Col2, Col3 from Patient

Omit the identity column in the insert and name include all other columns. The table drop and rename action likely require you to temporary drop the foreign key constraint

ALTER TABLE Patient NOCHECK CONSTRAINT ALL

and enable it again after the rename:

ALTER TABLE Patient WITH CHECK CHECK CONSTRAINT ALL

Reset identity column for existing values without deleting existing records

3 Answers3