Generating new row numbers in SQL based on a starting value

One of our support people came to me with an interesting task.  He needed to add rows from a SQL Server table to another table and populate a row number type of field.  That field needed to be generated by his SQL statements and start with the next number after the highest value already in the table.  And he couldn’t use an autoincrement field.

My first thought was to read the new values into a table variable with an column for that row number field.  Then iterate over that table with a cursor and populate that column.  That was a very bad thing and was quickly banished.  So I thought about it a bit and sent him the following example.

 -- The following temporary tables represent the 
 -- the data to be worked with. They are temp
 -- tables only to illustrate the code
create table #BaseTable
(
    ID int,
    Name varchar(64)
)

create table #NewData
(
    Name varchar(64)
)

 -- Lets throw some sample data into the base
 -- table.
insert #BaseTable(ID, Name) values (1, 'Joe')
insert #BaseTable(ID, Name) values (2, 'Jim')
insert #BaseTable(ID, Name) values (3, 'Jay')
insert #BaseTable(ID, Name) values (4, 'John')

 -- Lets populate the data table that we want
 -- to get the data from
insert #NewData(Name) values ('Ken')
insert #NewData(Name) values ('Keith')
insert #NewData(Name) values ('Kevin')

 -- Declare a variable to hold the maximum
 -- value of our row number field
declare @LastRow int

 -- Assign the max value to our variable
set @LastRow = (select MAX(ID) from #BaseTable)

 -- Add the rows into the destination tabkle
insert into #BaseTable(id, name)
 -- Use the rank() function to generate row
 -- numbers for the rows that we are adding
 -- and add the @LastRow to get the next highest
 -- values
select rank() over (order by n.Name) + @LastRow as rank, n.Name
from #NewData n
order by rank

 -- Show our merged results
select * from #BaseTable

 -- Example over, cleanup
drop table #NewData
drop table #BaseTable

To generate the row numbers from the merge table, we use the rank() function.  Rank() returns the rank of each row within the partition of the result set.  We specify the partition with the “over (order by n.name” clause.  If you have multiple fields to make a row unique, you would need to specify each field in the over clause.