0

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

Improve this question
6
  • 3
    So you have another table as a source? Because you can't have NULLs in a PK column. Commented May 16, 2019 at 9:47
  • Please share your table schema. Commented May 16, 2019 at 9:48
  • 1
    Tables have columns, not fields. Commented May 16, 2019 at 9:48
  • @GeorgeMenoutis The table ID column allows NULLs. So I copied data from another table into it that has no ID column, now I need to set PK values for them Commented May 16, 2019 at 9:54
  • By "incrementally", do you also want to use ID value "gaps"? eg if you have ID 1,5, and null, do you expect the NULL to take the value 2? Commented May 16, 2019 at 10:05

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

5 Comments

using option 1 gives this error >>> Windowed functions can only appear in the SELECT or ORDER BY clauses.
Sorry, I have removed option as there was some issue.
will re-creating the id, retain the previous ID
Yes. For implementing auto increment, you have to sacrifice those existing values. But if can manage new ids your self while inserting new records in future, I can then update existing all records ONLY with NULL value using CURSOR.
Do you have any other Column Or Combination of column in the table which is Unique?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.