SQL Server add auto increment primary key to existing table

Question

As the title  I have an existing table which is already populated with 150000 records  I have added an Id column  which is currently null    I m assuming I can run a query to fill this column with incremental numbers  and then set as primary key and turn on auto increment  Is this the correct way to proceed  And if so  how do I fill the initial numbers

User · Answer

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

User · Answer

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

User · Answer

You can t  turn on  the IDENTITY  it s a table rebuild   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

User · Answer

Try something like this  on a test table first     USE your database name GO WHILE  SELECT COUNT    FROM your table WHERE your id field IS NULL    0 BEGIN     SET ROWCOUNT 1     UPDATE your table SET your id field   MAX your id field  1 END PRINT  ALL DONE    I have not tested this at all  so be careful

User · Answer

ALTER TABLE table name ADD temp col INT IDENTITY 1 1   update

User · Answer

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  Properties  idendtity column

User · Answer

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   ALTER TABLE dbo YourTable    ADD ID INT IDENTITY   and then you can make it the primary key   ALTER TABLE dbo YourTable    ADD CONSTRAINT PK YourTable    PRIMARY KEY ID    or if you prefer to do all in one step   ALTER TABLE dbo YourTable    ADD ID INT IDENTITY        CONSTRAINT PK YourTable PRIMARY KEY CLUSTERED

User · Answer

I had this issue  but couldn t use an identity column  for various reasons   I settled on this   DECLARE  id INT SET  id   0  UPDATE table SET  id   id    id   1    Borrowed from here

User · Answer

This answer is a small addition to the highest voted answer and works for SQL Server  The question requested an auto increment primary key  the current answer does add the primary key  but it is not flagged as auto-increment  The script below checks for the columns  existence  and adds it with the autoincrement flag enabled   IF NOT EXISTS  SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    YourTable  AND COLUMN NAME    PKColumnName   BEGIN   ALTER TABLE dbo YourTable    ADD PKColumnName INT IDENTITY 1 1   CONSTRAINT PK YourTable PRIMARY KEY CLUSTERED  END  GO

User · Answer

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   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          However  you have to either delete the existing EMPLOYEE Table or do some adjustment according to your requirement

User · Answer

If your table has relationship with other tables using its primary or foriegen key  may be it is impossible to alter your table  so you need to drop and create the table again  To solve these problems you need to Generate Scripts by right click on the database and in advanced option set type of data to script to scheme and data  after that  using this script with the changing your column to identify and regenerate the table using run its query  your query will be like here   USE  Db YourDbName  GO SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO Drop TABLE  dbo   Tbl TourTable   CREATE TABLE  dbo   Tbl TourTable        ID   int  IDENTITY 1 1  NOT NULL       Name   nvarchar  50  NULL       Family   nvarchar  150  NULL     GO  SET IDENTITY INSERT  dbo   Tbl TourTable  ON   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1   INSERT  dbo   Tbl TourTable    ID    Name    Family   VALUES  1  name 1    family 1    SET IDENTITY INSERT  dbo   Tbl TourTable  off

User · Answer

This works in MariaDB  so I can only hope it does in SQL Server  drop the ID column you ve just inserted  then use the following syntax -  ALTER TABLE table name ADD id INT PRIMARY KEY AUTO INCREMENT   No need for another table  This simply inserts an id column  makes it the primary index  and populates it with sequential values  If SQL Server won t do this  my apologies for wasting your time

User · Answer

ALTER TABLE table name ADD COLUMN ID INT NOT NULL PRIMARY KEY AUTO INCREMENT   This could be useful

User · Answer

alter table       paste the tabal s name     add id int IDENTITY 1 1   delete from      paste the tabal s name     where id in      select a id FROM      paste the tabal s name   as a LEFT OUTER JOIN           SELECT MIN id  as id         FROM   paste the tabal s name           GROUP BY     paste the columns c1 c2             as t1  ON a id   t1 id   WHERE t1 id IS NULL     alter table       paste the tabal s name     DROP COLUMN id

User · Answer

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     UPDATE x SET x  lt Id gt    x New Id FROM     SELECT  lt Id gt   ROW NUMBER   OVER  ORDER BY  lt tablekey gt   AS New Id   FROM  lt tablename gt      x

[sql-server] SQL Server add auto increment primary key to existing table

Examples related to sql-server

Examples related to sql-server-2008

Examples related to primary-key

Examples related to alter-table