Random card insertion

[Zvoni]

Hey.
Are we talking on actually adding a new column named ROW_Number or are we talking about the Row_Number over function?
On both cases I have a gap on what to do. If, I think, we are talking about adding another column then what exactly do I do with rand?
"in the Range 1 To Count, comparing the result of RAND to ROW_NUMBER" I don't get it sorry.

ROW_NUMBER-Function

Imagine the CTE (or temp. table) contains the possible CardNumbers, say exactly 1M Numbers
2100010199999, 2100010200000,2100010200001.........2299999999999

Now you have a Table somewhere, which alread contains used CardNumbers (at least i understood you that way)
2100010200001, 2200011223344 and so....

You LEFT JOIN the temp. Table to that Table

SELECT TempTable.CardNumber, MyTable.CardNumberUsed 
FROM TempTable 
LEFT JOIN MyTable ON TempTable.CardNumber=MyTable.CardNumberUsed
WHERE MyTable.CardNumberUsed IS NULL

This returns all CardNumbers from TempTable, that DON'T already exist in MyTable

And on THAT Resultset you apply ROW_NUMBER

WITH CTE AS (SELECT TempTable.CardNumber, MyTable.CardNumberUsed 
FROM TempTable 
LEFT JOIN MyTable ON TempTable.CardNumber=MyTable.CardNumberUsed
WHERE MyTable.CardNumberUsed IS NULL) 
SELECT CTE.CardNumber, ROW_NUMBER() OVER(ORDER BY CTE.CardNumber) AS RN FROM CTE

This returns all "available" (unused) cardnumbers, with RN being something like a "virtual" ID, running from 1 to COUNT(CardNumbers)

And on THAT result you apply RAND

WITH 
CTE AS (SELECT TempTable.CardNumber, MyTable.CardNumberUsed 
FROM TempTable 
LEFT JOIN MyTable ON TempTable.CardNumber=MyTable.CardNumberUsed
WHERE MyTable.CardNumberUsed IS NULL),
C2 AS (SELECT CTE.CardNumber, ROW_NUMBER() OVER(ORDER BY CTE.CardNumber) AS RN FROM CTE),
MR AS (SELECT MAX(RN) AS MRN FROM C2)

SELECT C2.CardNumber FROM C2 
INNER JOIN MR ON 1=1
WHERE C2.RN=FLOOR(RAND()*(MR.MRN-1+1)+1);