5

I have a table and it already have a primary key, the data type is integer. The problem is the column is not set to auto increment once a row is being inserted. I have to check the max then add 1 to manually auto increment to column. Is there a way to alter the column to set the primary key to auto increment? I'm going to add hundreds of records and adding the id manually is quite a tedious task. I cannot drop the column since there are IDs that are already existing. Thanks in advance.

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
nigel
  • 101
  • 2
  • 2
  • 7
  • 2
    No, you **cannot** "add" the `IDENTITY` specification to an existing column. The only way to do this would be to create a new table with the same structure and an `IDENTITY` primary key column, then copy the data over into the new table, and rename it (and of course disable / re-enable all the foreign key relationships) – marc_s Mar 21 '14 at 05:49

2 Answers2

4

You can't change the definition of an existing column to be an identity. The closest you can come, while retaining any existing ids, is to;

  1. Create a new table with the same definition, but with the identity
  2. Configure the new table to allow inserts of specific values (using Set Identity_Insert On)
  3. Select data from the existing table into the new table
  4. Turn Identity_Insert back off
  5. Drop or rename the existing table
  6. Rename the new table to the original table's name.

Something like this

Create Table Table1 (id int, Name varchar(20))
Insert Into Table1 values (1,'Name 1')
Insert Into Table1 values (2,'Name 2') 


Create Table Table2 (Id int identity, Name varchar(20))
Go

Set Identity_Insert Table2 on
Go
Insert into Table2 (Id, Name) Select Id,Name from Table1
Set Identity_Insert Table2 Off
Go 
Exec sp_Rename Table1, TableOld, 'Object'
Exec sp_Rename Table2, Table1, 'Object'
Insert into Table1 (Name) values ('Name 3')
cmsjr
  • 53,391
  • 10
  • 68
  • 62
-6

Use This :

Alter TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY )

Vipin Mittal
  • 69
  • 4