Using ROW_NUMBER() to provide a unique row id

Not too long ago, I needed to populate a lookup table with the data that already existed in a referencing table. Yes, yes there is a problem with the original design of the database but that is another topic. However it did provide an opportunity to learn the ROW_NUMBER() function that was introduced in SQL Server 2005.

In the example, we’ll just create a source table that has a column `column1` that we’ll like to populate later into the lookup table.

create table source_table (
	column1 varchar(32),
	column2 varchar(32)
);

insert into source_table (column1, column2) values ('VAL1', 'VAL2');
insert into source_table (column1, column2) values ('VAL2', 'VAL3');
insert into source_table (column1, column2) values ('VAL3', 'VAL4');
insert into source_table (column1, column2) values ('VAL4', 'VAL5');
insert into source_table (column1, column2) values ('VAL4', 'VAL5');
insert into source_table (column1, column2) values ('VAL4', 'VAL5');

The next SQL is to query using the ROW_NUMBER() function.

select
	ROW_NUMBER() OVER (ORDER BY column1 DESC), column1, column2
from source_table

Next, we are going to create the lookup table that will be populated with the distinct column1 values from the source table. On a source table that has multiple distinct values for column1 this method sure beats writing the SQL by hand.

create table lookup_table (
	id int not null,
	code varchar(32) not null,
	constraint pk_lookup_table primary key (id)
);

insert into lookup_table(id, code)
select
	ROW_NUMBER() OVER (ORDER BY column1 ASC) + ISNULL((select MAX(id) from lookup_table), 0),
	column1
from (select distinct column1 from source_table) as source_table

select * from lookup_table

drop table source_table;
drop table lookup_table;

And here are the results for the lookup table.

id          code
----------- --------------------------------
1           VAL1
2           VAL2
3           VAL3
4           VAL4

(4 row(s) affected)