---
title: "SQL Trick: Pseudo-Identity Column After-the-fact"
description: Did you ever wish you could add a SQL Identity column (or, perhaps, just a sequence number) to a table after-the-fact? Here’s a simple trick to add pseudo-identity or sequence data to a table after-th
---

[RKL eSolutions | Technology Insights, Tips and Trends ](https://www.rklesolutions.com/blog)

# [SQL Trick: Pseudo-Identity Column After-the-fact](https://www.rklesolutions.com/blog/sql-pseudo-identity-column)

 Written by [RKL Team](https://www.rklesolutions.com/blog/author/rkl-team) | May 28, 2013 9:28:26 AM

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](https://www.rklesolutions.com/contact-us/) directly.

[View full post](https://www.rklesolutions.com/blog/sql-pseudo-identity-column)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "RKL Team"
  },
  "dateModified" : "2018-10-16T19:25:17.404Z",
  "datePublished" : "2013-05-28T09:28:26Z",
  "headline" : "SQL Trick: Pseudo-Identity Column After-the-fact",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.rklesolutions.com/blog/sql-pseudo-identity-column",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60.0,
      "url" : "https://www.rklesolutions.com/hubfs/RKL%20Logos/0001-5330483477089761811-1.png",
      "width" : 236.84212
    },
    "name" : "RKL eSolutions "
  }
}
```