Resetting IDENTITY Value in SQL Server – Step-by-Step Guide

The only way to guarantee a gapless series (assuming no deletes) is to serialize - almost always a bad idea

With a sequence or identity you cannot assume your series will be gapless as @a_horse mentions - but it looks like you are assuming that even with gaps, there is some relationship between sequence order and insertion order - this is not true either! A higher sequence number could be inserted before a lower. Therefore trying to join "consecutive" rows is a meaningless concept

example:

create sequence seq;
create table t1(id integer);
create table t2(id integer);

--session 1:
insert into t1 select seq.nextval from dual connect by level<1000000;
commit;

--session 2:
insert into t2 select seq.nextval from dual connect by level<1000000;
commit;

result:

select min(id), max(id) from t1 union all select min(id), max(id) from t2;

MIN(ID)                MAX(ID)                
---------------------- ---------------------- 
1                      1671679                
356547                 1999998               

One option is to create a staging table with a new ID as Row_number(). Consider the below sql:

begin transaction createstaging

select row_number() over (order by existingID) newIDValue, DataColA, DataColB, ...
into staging_table
from source_table 

truncate table source_table

commit transaction createstaging
GO

set identity_insert source_table on 
GO

insert source_table
(existingID, DataColA, DataColB, ...)
select 
newIDValue, DataColA, DataColB, ...
from staging_table 
order by newIDValue

GO

set identity_insert source_table off
GO

drop table staging_Table
GO

The truncate table in combination with the identity_insert should mean that you do not have to manually reset the identity seed on the source table.