3

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.

user2541172
  • 59
  • 1
  • 2
  • 6
  • 3
    how did you add the primary key column?. You need to set the identity seed and increment value, did you do that? If you add identity, the column will NOT be null,it will generate the value automatically – Appyks Oct 09 '13 at 04:19
  • possible duplicate of [Forgot to add primary key column as Identity](http://stackoverflow.com/questions/18996250/forgot-to-add-primary-key-column-as-identity) – Nithesh Narayanan Oct 09 '13 at 04:25
  • @Nithesh.. in the link that you marked duplicate.. I think it says how to add primary key.. But the question is on how to fill the NULL value of the PK field incrementally in a single query. – Pradip Oct 09 '13 at 04:34

5 Answers5

2

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

Shahid Iqbal
  • 2,095
  • 8
  • 31
  • 51
1

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)
Pradip
  • 1,507
  • 11
  • 28
1

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)
Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
0
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
Appyks
  • 496
  • 3
  • 12
0

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

Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26