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.