Actual table has about 6 million rows, num1 and num2 in each row represent numeric boundaries. I need to convert the two-column set into a single column, and applying the to each of the two rows that previously shared a single row. Below is a small sample. I looked at Unpivot examples but nothing seems to fit what I need. Can anyone recommend the right way to go? I realize I will end up with 12 million rows in the end.
Thanks.
declare @orig table ( num1 bigint , num2 bigint , metakey tinyint )
insert into @orig
select 7216,7471 , 0
union all
select 7472,8239 , 1
union all
select 8240,9263 , 2
union all
select 9264,11311 , 3
declare @dest table ( allnum bigint , metakey tinyint )
-- Wanted result set:
/*
select * from @dest
7216 0
7471 0
7472 1
8239 1
8240 2
9263 2
9264 3
11311 3
*/
I understand that this works for my table variable sample, but for the really big set it does not smell right:
insert into @dest
select num1 , metakey
from @orig
union all
select num2 , metakey
from @orig
order by 1
Unpivot seams to do what you want.
select u.allnum,
u.metakey
from @orig as o
unpivot (allnum for col in (o.num1, o.num2)) as u
Compared to the union query you already have this should be faster because it will only scan the table once instead of twice.