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?

marc_s · Accepted Answer · 2017-07-14T09:53:10.177

534

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

edited Jul 14 '17 at 09:53

answered Feb 01 '11 at 12:15

marc_s

732,580
175
1,330
1,459

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. – daveagp Feb 17 '12 at 19:09
The Identity keyword is SQL Server specific. Auto_Increment is the MySQL version as you found. http://stackoverflow.com/questions/10283780/equivalent-of-mssql-identity-column-in-mysql – AndyMcKenna Dec 03 '12 at 13:46
2

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. http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafysqlpidentity.htm – user1477388 Mar 08 '13 at 18:22
3

I just used this and it seems to have worked `alter table attachments add ATTACHMENT_NUMBER int identity (1000, 1)` – user1477388 Mar 08 '13 at 18:31
while creating a new table i used `BookID int primary key identity` i didn't specify both seed and increment , is that good practice ? cause i see documentation in [MSDN](https://msdn.microsoft.com/en-us/library/ms186775.aspx) If neither is specified, the default is (1,1) . – Shaiju T Oct 28 '15 at 13:23
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. – marc_s Oct 28 '15 at 13:59
@marc_s , thank you, one last question, is mentioning not null necessary for good practice like: `BookID int not null identity(1,1) primary key` , because i see in `w3schools` [here](http://www.w3schools.com/sql/sql_autoincrement.asp) they dont mention, but in [this](http://stackoverflow.com/a/6777853/2218697) post, not null is used while creating new table ? – Shaiju T Nov 01 '15 at 08:45
1

@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 – marc_s Nov 01 '15 at 09:16
This is a wonderful answer. Since I am adding the primary key by altering the table, do i have to add the clustered index explicitly or it will be created automatically? – turbo88 Dec 01 '15 at 22:23
3

@turbo88: when you define your `PRIMARY KEY`, the clustered index is created automatically for you (unless you explicitly specify `NONCLUSTERED`) – marc_s Dec 02 '15 at 05:48
1

I dropped the current index and id column, then re-added it using this method. It worked flawlessly. – Jeff Bluemel Dec 10 '15 at 23:04
If you've created a table and just want set the number that the auto-increment starts from (like changing the default of 1 to 1000) use ALTER TABLE `test` auto_increment = 1000; – Wesley Smith Aug 09 '16 at 04:37

score 23 · Answer 2 · answered Feb 01 '11 at 12:17

23

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.

answered Feb 01 '11 at 12:17

gbn

422,506
82
585
676

5

The OP is on SQL Server 2008 [so there is a way](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661) – Martin Smith May 22 '11 at 17:46
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. – Martin Smith May 22 '11 at 17:52

score 20 · Answer 3 · answered Sep 20 '12 at 17:38

20

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.

answered Sep 20 '12 at 17:38

Kevin

1,723
2
17
16

score 9 · Answer 4 · answered Jul 14 '14 at 14:50

9

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

answered Jul 14 '14 at 14:50

Renzo Ciot

3,746
2
25
29

You saved me time with this one! Good addendum to @gbn's answer. – Kristen Waite Jul 21 '16 at 11:30

score 6 · Answer 5 · answered Feb 06 '14 at 10:26

6

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

answered Feb 06 '14 at 10:26

user3279092

61
1
1

score 6 · Answer 6 · answered Mar 21 '18 at 14:01

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

That's sad, a lot of up-votes on the post, your answer fills 100% for me, wish I can add more up-votes — Sarz, Jul 21 '20 at 17:12

score 6 · Answer 7 · edited Jun 01 '20 at 05:32

6

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

edited Jun 01 '20 at 05:32

Community

1
1

answered Jan 28 '20 at 07:42

sandesh jain

61
1
2

6

can you explain it? – Muhammad Dyas Yaskur Jan 28 '20 at 08:09
1

While this code may resolve the OP's issue, it is best to include an explanation as to how your code addresses the OP's issue. In this way, future visitors can learn from your post, and apply it to their own code. SO is not a coding service, but a resource for knowledge. Also, high quality, complete answers are more likely to be upvoted. These features, along with the requirement that all posts are self-contained, are some of the strengths of SO as a platform, that differentiates it from forums. You can edit to add additional info &/or to supplement your explanations with source documentation. – ysf Jun 01 '20 at 19:49
1

I arrived here, via a google search, because I only needed the syntax. This was the best answer for me, possibly many others without SO accounts. I ignored the accepted answer because it was too long! My point is that there are two types of visitor - one looking for a full explanation and one using SO as a quick reference. What would be useful is answers having a TLDR section. – MortimerCat May 19 '21 at 09:01

score 4 · Answer 8 · answered Jul 31 '17 at 18:55

4

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

answered Jul 31 '17 at 18:55

gustavo herrera

51
3

1

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. – jinglesthula Jul 31 '17 at 19:20
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. – gustavo herrera Aug 03 '17 at 17:51

score 2 · Answer 9 · answered Oct 27 '17 at 08:02

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

score 0 · Answer 10 · answered Jul 20 '16 at 15:40

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.

score 0 · Answer 11 · edited Jul 27 '16 at 13:33

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.

score 0 · Answer 12 · answered Nov 17 '19 at 11:28

0

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

answered Nov 17 '19 at 11:28

Mohamed Sakilani

11

What's your question? [How to ask](https://stackoverflow.com/help/how-to-ask) – Ann Kilzer Nov 17 '19 at 11:35
1

Edit your answer, using markdown to properly format your code example. – Bill Keller Nov 17 '19 at 11:59

Road Warrior · Answer 13 · 2016-11-26T16:33:10.600

-1

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.

edited Nov 26 '16 at 16:33

answered Nov 26 '16 at 16:27

Road Warrior

21
2

score -1 · Answer 14 · answered Aug 29 '21 at 09:55

-1

Try This Code Bellow:

DBCC CHECKIDENT ('settings', RESEED, 0)

answered Aug 29 '21 at 09:55

Mahmoud abo ghanam

1

score -3 · Answer 15 · answered Feb 01 '11 at 12:22

-3

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!

answered Feb 01 '11 at 12:22

PacDemon

11
1

1

-1 Doesn't answer the question (which is about adding `IDENTITY` columns) and wouldn't work anyway. `UPDATE your_table SET your_id_field = MAX(your_id_field)+1` you can't just chuck in `MAX` there. Where is a `WHERE` clause to avoid just repeatedly updating the same row? – Martin Smith Oct 07 '13 at 11:51

score -4 · Answer 16 · answered Mar 21 '18 at 14:05

-4

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

answered Mar 21 '18 at 14:05

Killer

47
3

SQL Server add auto increment primary key to existing table

16 Answers16

Linked

Related