Did you ever wish you could add a SQL Identity column (or, perhaps, just a sequence number) to a table after-the-fact? Perhaps the table has been populated elsewhere in a stored procedure or by some other method and data was not available (or convenient) to provide a unique row identity at the time.
Now, perhaps, you want to loop through the rows in the table sequentially (or, at least, process each row only once), but there is no existing unique data that would allow you to sequence or identify the rows to manage the looping structure.
Here’s a simple trick to add pseudo-identity or sequence data to a table after-the-fact:
/*********************************************************************************
How to add an after-the-fact "identity" column to an existing table
**********************************************************************************/
create table #InvcHdr
( Info varchar(13) )
insert into #InvcHdr ( Info ) values ('A100')
insert into #InvcHdr ( Info ) values ('B101')
insert into #InvcHdr ( Info ) values ('B101')
insert into #InvcHdr ( Info ) values ('A100')
-- The table as created
select * from #InvcHdr
/*********************************************************************************
Now we have a table, but we can’t use “greater than” or “less
than” in our looping structure to process each row only once.
There are duplicates in our ‘Info’ column. The data is not
unique in a way that allows sequencing of the loop.
Therefore, we are going to add a column for sequencing.
**********************************************************************************/
alter table #InvcHdr
add SequenceNo int
-- The table after adding the SequenceNo column
select * from #InvcHdr
/*********************************************************************************
Now we need to add our pseudo-identity or sequence data to
the new column.
**********************************************************************************/
-- Adding the Sequence Numbers to the table
declare @Identity int
select @Identity = 0
update #InvcHdr
set SequenceNo = @Identity
, @Identity = @Identity + 1
/*********************************************************************************
Now let’s look at the contents of our table with the pseudo-identity
column data included.
**********************************************************************************/
-- The table with the new SequenceNo column populated
select * from #InvcHdr
drop table #InvcHdr
You might find this useful sometime. I hope you do.
Please leave your comments or questions here, or feel free to contact us directly.