How to join sequential numbers to unrelated data (SQL Server)

Question

This question is a followup to a previous question I had about discovering unused sequential number ranges without having to resort to cursors (Working with sequential numbers in SQL Server 2005 without cursors). I'm using SQL Server 2005.

What I need to do with those numbers is to assign those numbers to records in a table. I just can't seem to come up with a way to actually relate the numbers table with the records that need those numbers.

One possible solution that came to mind was insert the records in a temp table using an identity and using the beginning of the number range as an identity seed. The only problem with this approach is that if there are gaps in the number sequence then I'll end up with duplicate control numbers.

This is how my tables look like (overly simplified):

Numbers table:

Number      
-------
102314
102315
102319
102320
102324
102329 

Data table:

CustomerId   PaymentAmt   ControlNumber
----------   ----------   -------------
1001         4502.01      NULL
1002         890.00       NULL
9830         902923.34    NULL

I need a way to make it so i end up with:

CustomerId   PaymentAmt   ControlNumber
----------   ----------   -------------
1001         4502.01      102314
1002         890.00       102315
9830         902923.34    102319

Is this possible without having to use cursors? The reason I'm avoiding cursors is because our current implementation uses cursors and since its so slow (8 minutes over 12,000 records) I was looking for alternatives.

Note: Thanks to all who posted answers. All of them were great, I had to pick the one that seemed easier to implement and easiest to maintain for whomever comes after me. Much appreciated.

Answer 1

Try this:

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY CustomerId) Corr
    FROM DataTable
)

UPDATE CTE
SET CTE.ControlNumber = B.Number
FROM CTE
JOIN (  SELECT Number, ROW_NUMBER() OVER(ORDER BY Number) Corr
        FROM NumberTable) B
ON CTE.Corr = B.Corr

Answer 2

Buidling on Martin's code from the linked question, you could give all rows without control number a row number. Then give all unused numbers a row number. Join the two sets together, and you get a unique number per row:

DECLARE @StartRange int, @EndRange int
SET @StartRange = 790123401
SET @EndRange = 790123450;

; WITH  YourTable(ControlNumber, CustomerId) AS
        (
        SELECT  790123401, 1000
        UNION ALL SELECT  790123402, 1001
        UNION ALL SELECT  790123403, 1002
        UNION ALL SELECT  790123406, 1003
        UNION ALL SELECT  NULL, 1004
        UNION ALL SELECT  NULL, 1005
        UNION ALL SELECT  NULL, 1006
        )
,       YourTableNumbered(rn, ControlNumber, CustomerId) AS
        (
        select  row_number() over (
                    partition by IsNull(ControlNumber, -1) 
                    order by ControlNumber)
        ,       *
        from    YourTable
        )
,       Nums(N) AS
        (
        SELECT @StartRange
        UNION ALL
        SELECT N+1
        FROM Nums
        WHERE N < @EndRange
        )
,       UnusedNums(rn, N) as
        (
        select  row_number() over (order by Nums.N)
        ,       Nums.N
        from    Nums
        where   not exists
                (
                select  *
                from    YourTable yt
                where   yt.ControlNumber = Nums.N
                )
        )
select  ytn.CustomerId
,       IsNull(ytn.ControlNumber, un.N)
from    YourTableNumbered ytn
left join
        UnusedNums un
on      un.rn = ytn.rn
OPTION (MAXRECURSION 0)