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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.<Id> = x.New_Id
FROM (
  SELECT <Id>, ROW_NUMBER() OVER (ORDER BY <tablekey>) AS New_Id
  FROM <tablename>
  ) x

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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 

Answer 9

ALTER TABLE table_name ADD temp_col INT IDENTITY(1,1) 
update 

Answer 10

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.

Answer 11

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.

Answer 12

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

Answer 13

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!

Answer 14

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.

Answer 15

ALTER TABLE table_name ADD COLUMN ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT ; This could be useful