This post is part 2 in our series of generating sequential numbers in SQL Server. In this post we are going to increase the requirements of our example and add a bit more complexity. We start with a similar table as in the previous post but we have added new columns. We need to create sequence numbers in the column [refnum]. This example represents a batch of entries by three people. Perhaps these people are reporting sales numbers. I have not included that column in this table, however. I have created a new database called [Sequence] and then I ran this script to create our starter table.

USE [Sequence]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[People05]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[People05]
create table People05
(
unqid int IDENTITY(1,1),
firstname varchar(15),
refnum varchar(12),  -- represents any kind of internal coding that repeats
dupseq int
)
insert into People05 values('Bob','CodeAG',0)
insert into People05 values('Bob','CodeAG',0)
insert into People05 values('Bob','CodeAG',0)
insert into People05 values('Jill','CodeAG',0)
insert into People05 values('Sally','CodeAG',0)
insert into People05 values('Sally','CodeAG',0)
select * from People05

People05

Here is the table we are starting with. Our objective is to add three-digit sequence numbers to the end of the column refnum that start at 1 and increase by one as long as the name does not change. There are 3 “Bob” records, so we will append 001, 002 and 003 to the end of each refnum.

We need to append sequence numbers to the column [refnum] for each of the three people. How do we do that?

update People05 
Set People05.dupseq=R.dupseq
from(select unqid,ROW_NUMBER() over (partition by S.firstname  order by S.unqid) as dupseq
		from People05 S) R
where People05.unqid=R.unqid

select * from People05

update People05 
set refnum = refnum + right('000' + cast(dupseq as varchar(3)),3)

select * from People05

Below are the results of the above two SELECT statements.

At the heart of this program is the ROW_NUMBER() function. If we isolate that part and simply run the following code, we get the results shown below. I took out the alias “S” in the code below, but exactly the same results occur with the alias included.

select unqid, ROW_NUMBER() over (partition by firstname  order by unqid) 
	as dupseq
	from People05 

What’s Next?

Our example uses an incoming table that is a batch. There were previous batches. There will be more batches. We therefore need to record all of the batches that we have received so far and append this new batch (People05) to the table that contains all of the batches.

Here’s the tricky part: we need to take into account all of the previous batches for each person and start our sequencing at the next number. For example if Bob had 1 previous entry, we will need to start his sequencing at 2 for this incoming file. We need to get the maximum value of all of Bob’s previous sequences. We need another column in our incoming table [People05] to store the maximums. The default value will be zero. We will search the [AllPeople] table for previous entries for Bob and the others and update the maximums.

Below is our [AllPeople] table that contains all of the previous records, and below that is the sql script that creates the table and inserts our data.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AllPeople]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[AllPeople]
create table AllPeople
(
firstname varchar(15),
refnum varchar(12),  -- represents any kind of internal coding that repeats
)
insert into AllPeople values('Johnny','CodeAA001')
insert into AllPeople values('Linda','CodeAA001')
insert into AllPeople values('Bob','CodeAB001')
insert into AllPeople values('Jill','CodeAC001')
insert into AllPeople values('Jill','CodeAD002')
insert into AllPeople values('Jill','CodeAE003')
insert into AllPeople values('Samuel','CodeAF001')