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):
Number ------- 102314 102315 102319 102320 102324 102329
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.
;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