Rectangle 27 0

SQL Server add auto increment primary key to existing table?


DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1

I had this issue, but couldn't use an identity column (for various reasons). I settled on this:

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


Do you have info on whether this is a good approach? The OP is asking whether it's the "correct way to proceed". A more complete answer could help them know the pros/cons of the approach.

Really I'm using this option in development environment, if you wnat pass this change to production, you should vericate with VIEW DEPENDENCY if the identity field is being used by some Store procedure o trigger.

by the designer you could set identity (1,1) right click on tbl => desing => in part left (right click) => properties => in identity columns select #column

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


When we add and identity column in an existing table it will automatically populate no need to populate it manually.

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


UPDATE x
SET x.<Id> = x.New_Id
FROM (
  SELECT <Id>, ROW_NUMBER() OVER (ORDER BY <tablekey>) AS New_Id
  FROM <tablename>
  ) x

If the column already exists in your table and it is null, you can update the column with this command (replace id, tablename, and tablekey ):

You saved me time with this one! Good addendum to @gbn's answer.

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
ALTER TABLE dbo.YourTable
   ADD CONSTRAINT PK_YourTable
   PRIMARY KEY(ID)
ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED
alter table attachments add ATTACHMENT_NUMBER int identity (1000, 1)

@stom: if you don't specify anything, seed=1 and increment=1 will be used - which is the most frequently used setting anyway. If you created a table like this - it'll work just fine. But I would recommend to always explicitly specify the seed and increment in your SQL scripts - especially for a larger site. It's just good practice.

@stom: well, the PRIMARY KEY implies that NOT NULL is in place - so again - it's not absolutely necessary. But I prefer to be explicit and so I always have the NOT NULL there, just to be absolutely clear

@turbo88: when you define your PRIMARY KEY, the clustered index is created automatically for you (unless you explicitly specify NONCLUSTERED)

No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:

This is a really good answer, but how can I change the starting integer from 1 to 1000? I would like to start counting at 1000. I suspect I can use ALTER TABLE ORDER ALTER COLUMN ORDERNO RESTART WITH 1 but I didn't want to try it without checking with an expert :) Ref. pic.dhe.ibm.com/infocenter/iseries/v7r1m0/

and then you can make it the primary key:

or if you prefer to do all in one step:

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


@Martin: interesting we can mess around with system tables again...

If you don't care about the number order, you'd add the column, NOT NULL, with IDENTITY in one go. 150k rows isn't a lot.

If you need to preserve some number order, then add the numbers accordingly. Then use the SSMS table designer to set the IDENTITY property. This allows you to generate a script which will do the column drop/add/keep numbers/reseed for you.

The OP is on SQL Server 2008 so there is a way

The whole instance needs to be started in single user mode so probably not viable for most circumstances but the ALTER TABLE ... SWITCH can do it without that.

You can't "turn on" the IDENTITY: it's a table rebuild.

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


Here is an idea you can try. Original table - no identity column table1 create a new table - call table2 along with identity column. copy the data from table1 to table2 - the identity column is populated automatically with auto incremented numbers.

rename the original table - table1 to table3 rename the new table - table2 to table1 (original table) Now you have the table1 with identity column included and populated for the existing data. after making sure there is no issue and working properly, drop the table3 when no longer needed.

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


CREATE TABLE EMPLOYEES(

    EmpId        INT NOT NULL IDENTITY(1,1), 
    F_Name       VARCHAR(20) ,
    L_Name       VARCHAR(20) ,
    DOB          DATE ,
    DOJ          DATE ,
    PRIMARY KEY (EmpId),
    DeptId int FOREIGN KEY REFERENCES DEPARTMENT(DeptId),
    DesgId int FOREIGN KEY REFERENCES DESIGNATION(DesgId),
    AddId int FOREIGN KEY REFERENCES ADDRESS(AddId)   
)

Create a new Table With Different name and same columns, Primary Key and Foreign Key association and link this in your Insert statement of code. For E.g : For EMPLOYEE, replace with EMPLOYEES.

However, you have to either delete the existing EMPLOYEE Table or do some adjustment according to your requirement.

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
ALTER TABLE dbo.YourTable
   ADD CONSTRAINT PK_YourTable
   PRIMARY KEY(ID)
ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED
alter table attachments add ATTACHMENT_NUMBER int identity (1000, 1)

@stom: if you don't specify anything, seed=1 and increment=1 will be used - which is the most frequently used setting anyway. If you created a table like this - it'll work just fine. But I would recommend to always explicitly specify the seed and increment in your SQL scripts - especially for a larger site. It's just good practice.

@stom: well, the PRIMARY KEY implies that NOT NULL is in place - so again - it's not absolutely necessary. But I prefer to be explicit and so I always have the NOT NULL there, just to be absolutely clear

@turbo88: when you define your PRIMARY KEY, the clustered index is created automatically for you (unless you explicitly specify NONCLUSTERED)

I am using the phpMyAdmin interface. I was getting SQL errors when trying to use the 'IDENTITY' keyword, which was also unavailable in the GUI. It worked when I added a new row with AUTO_INCREMENT selected and its type as INT PRIMARY.

No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:

The Identity keyword is SQL Server specific. Auto_Increment is the MySQL version as you found. stackoverflow.com/questions/10283780/

This is a really good answer, but how can I change the starting integer from 1 to 1000? I would like to start counting at 1000. I suspect I can use ALTER TABLE ORDER ALTER COLUMN ORDERNO RESTART WITH 1 but I didn't want to try it without checking with an expert :) Ref. pic.dhe.ibm.com/infocenter/iseries/v7r1m0/

and then you can make it the primary key:

or if you prefer to do all in one step:

Note
Rectangle 27 0

SQL Server add auto increment primary key to existing table?


DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1

I had this issue, but couldn't use an identity column (for various reasons). I settled on this:

Note