• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

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.
1 Solution
QlemoC++ DeveloperCommented:
According to SQL ServerCentral (http://www.sqlservercentral.com/Forums/Topic126147-8-1.aspx), 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 column....you can go for this..


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now