Applying Identity auto increment constraint

Hi All,

i have couple of tables in my database that does not have identity auto incerement constraint defined on its primary key column. but these tables has lot data init.

I would like to apply this constraint to its primary ket column but at the same time i dot not want to loss the existing keys (values).

Please suggest me the best approach to acheive this.
Who is Participating?
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
According to SQL ServerCentral (, there is no other way than to copy the table into a new one, having the column defined as identity and identity_insert on that table switched on. That is what Enterprise Manager / Management Studio do, and there seems to be no alternative.

It might be better (performance wise) to:
* add a new (identity) column,
* set identity_insert on,
* update the identity column with the primary key
* drop the PK constraint
* drop the PK column
* rename the identity column
* set the PK
if the table is very big. I can't tell whether the copy or the latter is faster, but both require you to have the table offline for a while.
You can't add identity for existing column with data, first you need to create new table with same set of columns with identity column and the identity seed set as max value + 1 of your existing data and then switch off the identity and import the data using DTS package. after import switch on the identity.
you must need to add a new column and set it as identity column..

if you dont want to loose data of your existing column....

anither way of doing this is...

if you dont want to add new can go for this..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.