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

I see a solution to this was accepted a while ago, but I thought I'd add another approach since I'm in a similar position.

The accepted solution is simpler, but does not offer the ability to control the ordering (like preserving chronological ordering of a datetime column as is my case).

In SQL Server, my solution was to:

1. create a new table from entries in the old (so that I could specify ordering) including the new numbered column
2. set the new numbered column as primary key
3. drop the old table
4. rename the new table

Code:

SELECT IDENTITY(INT, 1, 1) AS id, OldTable.*
INTO TempOldTable
FROM OldTable
ORDER BY OldTable.start_time;

-- set new numbered column as primary key
ALTER TABLE TempOldTable
ADD CONSTRAINT PK_TempOldTable PRIMARY KEY (id);

-- Optionally, verify new table data
-- SELECT * FROM TempOldTable;

DROP TABLE OldTable;

--rename new table
EXEC sp_rename 'TempOldTable', 'OldTable';

Full disclosure: I arrived at this solution through some active researching (this post helped), and some interrogation of ChatGPT.

Answer 2

ALTER TABLE table_name ADD COLUMN ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT ;

This could be useful

Answer 3

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 4

Try This Code Bellow:

DBCC CHECKIDENT ('settings', RESEED, 0) 

Answer 5

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

Answer 6

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 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

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 10

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 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

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 13

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 14

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

Answer 15

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 16

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 17

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.