How to Put a Sequence in a Column Already filled

How to Put a Sequence in a Column Already filled

Suppose we would like to create a sequence in a table already filled with data. I found out this solution to be pretty easy:
CREATE TABLE SEQ (COK NVARCHAR(10)) 
insert into seq values ('York'),('Boston'),('Chicago') 
select * from seq 
--step 1, I create a table and populate it with three rows now I count the rows 
select count(*) from seq 
--and start my sequence from the rowcount+1 
create sequence myseq 
as int 
start with 4 
increment by 1 
--I add a new column 
alter table seq  
add conta int 
--I add a constraint 
alter table seq 
add constraint addseq 
default (next value for myseq) for [conta] 
--Insert a new row 
Insert into seq values ('Seattle',default) 
--Select again 
select * from seq 
--as you can see we have three rows with null value. They are the rows inserted before the constraint 
declare @co int 
select @co=count(*) from seq where conta is null 
declare @inc int 
set @inc=1 
while (@inc<=@co) 
begin 
declare @city as nvarchar(10) 
select top 1 @city=cok from seq where conta is null 
update seq set conta=@inc where cok=@city 
set @inc=@inc+1 
end 
--now all the rows follow a sequence
 The first step is to count the rows already existing, the second step is to create a new column, the third (the most important) to create a sequence that starts from the count value +1. At this point the sequence starts but now we need to fill the value null. This short code is really useful for a lot of situations. We count the rows one for one and we update the rows (in this case from 1 to rowcount). This last code can be used also when you want create a new sequence without create a sequence ( if you don't care to insert any other row you can use the code to populate the column with an incremental order).  


See Also


Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • When the table has a Clustered Index on COK column, the TOP1 behaves differently and the sequence values assigned will be different.

    Try creating an index just before running the while loop and check the difference.

    create clustered index IX_SEQ on SEQ(COK asc)

  • When the table has a Clustered Index on COK column, the TOP1 behaves differently and the sequence values assigned will be different.

    Try creating an index just before running the while loop and check the difference.

    create clustered index IX_SEQ on SEQ(COK asc)

Page 1 of 1 (2 items)