CODEWITHSUNDEEP
sqlsql-server
user10445503
user10445503

Reputation: 165

Alter table to add incrementally primary key records where ID values are null

I have a table in sql server with ID field as the primary key. In the rows of the ID field, some have primary key values while some rows do not have primary key values because the ID column allows null. Now I want to run a query to insert values incrementally into the rows that are null so that they can have primary key values. I have tried using an ALTER command but no head way

Upvotes: 0

Views: 527

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can DROP that column and ADD again with Auto Increment value.

ALTER TABLE your_table DROP COLUMN ID
ALTER TABLE your_table ADD ID INT IDENTITY(1,1)

This will generate all values from the start and as a result you will lose existing value (upto 6).

Upvotes: 0

Sergey M
Sergey M

Reputation: 169

because you didn't provide any table structure description and we don't know if there are any business key or some unique combinations of data exists to identify a row without primary key then the easiest way, imho, is to use update cursor:

begin tran
-- rollback
-- commit

select * from [Table_1] where id is null

declare @Id int, @i int = 0
    ,@MaxId int

set @MaxId = (select Max(Id) from [Table_1] )
declare Update_cur cursor local
    for select Id from [Table_1] where id is null
    for update of Id

open Update_cur
fetch next from Update_cur into @Id
while @@FETCH_STATUS = 0 begin
    set @i += 1
    update [Table_1] set Id = @MaxId + @i  where CURRENT OF Update_cur
    fetch next from Update_cur into @Id
end

close Update_cur
deallocate Update_cur

select * from [Table_1] order by Id

P.S. don't forget to commit or rollback transaction after performing tests

Upvotes: 1

Related Questions