CODEWITHSUNDEEP
sql-servervb.net
user2541172
user2541172

Reputation: 59

Adding auto incrementing primary key to an existing table in SQL SERVER 2005

I have a table with 8,000 rows of data and will be adding more. but I forgot to put a primary key in the beginning. so that each row has a unique key. later i added a primary key column. but that column is NULL now.

I want the first row to start at ID 1 and increment all the way up to the last row at ID 8000. How do I update all of the rows with a single query?

i am using SQL Server 2005.

Upvotes: 3

Views: 10823

Answers (5)

Shahid Iqbal
Shahid Iqbal

Reputation: 2135

Open Table Design, Add New Column u want Select the column and in Properties In Identity Specification make (Is Identity) Yes.. You can start from where you want by setting the Identity Seed property, by Default it starts from 1.

If you have already Identity Column u can also update it.
Step 1: Remove Identity Specification from Column in Table Design.
Step 2: Use Cursor to Update table Column starting from 1.
Step 3: Again apply Identity Specification on Column in Table Design
Step 4: By query reset Identity Column, from the value u want. e.g
 DBCC CHECKIDENT("TableName",Reseed,8000);
so the next identity value will be 8001.

enter image description here

Upvotes: 2

Lloyd Banks
Lloyd Banks

Reputation: 36638

Get rid of the column you just added. Then run this

ALTER TABLE table
ADD ID INT IDENTITY

ALTER TABLE table
ADD CONSTRAINT PK_table
PRIMARY KEY(ID)

Upvotes: 1

Pradip
Pradip

Reputation: 1537

As per your requirement you just needed a sql query that will update the entire table with the new primary key values in an incremental fashion. Here is is :

UPDATE myTable
SET ID = ID + 1

Where ID is the PK field name

Once Updated do not forget to add identity column as shown below :

ALTER TABLE table
ADD ID INT IDENTITY

ALTER TABLE table
ADD CONSTRAINT PK_table
PRIMARY KEY(ID)

Upvotes: 1

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

You can update table based on unique key. Declare @i int=1 While(@i <=(select count(*) from table)) Begin

Update table Set primary_key_column=@i Where

Set@i=@i+1 End

Upvotes: 0

Appyks
Appyks

Reputation: 496

CREATE TABLE [dbo].[TheTable](
    [TheID] [int] IDENTITY(1,1) NOT NULL,
    [TheColumn] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TheTable] PRIMARY KEY CLUSTERED 
(
    [TheID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO [dbo].[TheTable]
        ( [TheColumn] )
SELECT 'one'
UNION 
SELECT 'two'

SELECT * FROM [dbo].[TheTable] TT

Upvotes: 0

Related Questions