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

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 impl开发者_运维百科ement and easiest to maintain for whomever comes after me. Much appreciated.

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

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)          

All you need is a deterministic order in data table. If you have that, you can use ROW_NUMBER() as a join condition:

with cte as (
  select row_number() over (order by CustomerId) as [row_number],
         ControlNumber
  from [Data Table]
         where ControlNumber is null),
    nte as (
  select row_number() over (order by Number) as [row_number],
         Number
  from [Numbers])
update cte
  set ControlNumber = Number
from cte 
join nte on  nte.[row_number] = cte.[row_number];

If you need it to be concurency proof, it does get more complex.

EDITED added in code to remove used values from @Number, via the OUTPUT caluse of the UPDATE and a DELETE

try using ROW_NUMBER() to join them:

DECLARE @Number table (Value int)
INSERT @Number VALUES (102314)
INSERT @Number VALUES (102315)
INSERT @Number VALUES (102319)
INSERT @Number VALUES (102320)
INSERT @Number VALUES (102324)
INSERT @Number VALUES (102329)

DECLARE @Data table (CustomerId int, PaymentAmt numeric(10,2),ControlNumber int)
INSERT @Data VALUES (1001,         4502.01      ,NULL)
INSERT @Data VALUES (1002,         890.00       ,NULL)
INSERT @Data VALUES (9830,         902923.34    ,NULL)

DECLARE @Used table (Value int)

;WITH RowNumber AS 
(
SELECT Value,ROW_NUMBER() OVER(ORDER BY Value) AS RowNumber FROM @Number

)
,RowData AS
(
SELECT CustomerId,ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNumber, ControlNumber FROM @Data WHERE ControlNumber IS NULL
)
UPDATE d
    SET ControlNumber=r.Value
    OUTPUT r.Value INTO @Used
    FROM RowData d
        INNER JOIN RowNumber r ON d.RowNumber=r.RowNumber

DELETE @Number WHERE Value IN (SELECT Value FROM @Used)

SELECT * FROM @Data
SELECT * FROM @Number

OUTPUT:

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

(3 row(s) affected)

Value
-----------
102320
102324
102329

(3 row(s) affected)

You'll need something to join the two tables together. Some data value that you can match between the two tables.

I'm assuming there's more to your numbers table than just one column of numbers. If there's anything in there that you can match to your data table you can get away with an update.

How are you updating the data table using cursors?