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

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 7

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 8

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

Answer 9

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