Share via

How to Put a Sequence in a Column Already Filled

  • Article

Suppose we would like to create a sequence in a table already filled with data. I found 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 already existing rows. The second step is to create a new column. The third (the most important) step is to create a sequence that starts from the count value +1. At this point the sequence starts but now we need to fill it with the null value. This short code is really useful for a lot of situations. We count the rows one by one and we update the rows (in this case from 1 to rowcount). This code can also be used when you want to create a new sequence without creating 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