Random card insertion

[sapator]

Hi.
This is for later this year but might as well be prepared.
We need to issue a couple of million of cards that would be random and the random part to be from a number and up.
So for example I have a card: 2100000199999 , the random cards would be something like 2100009199999 , 2140000199999,2103560199999 etc.
So it should be 21 as a starting point and over 2100000199999 .
Now I have this code that if I remember correctly was from JMC and I modified it so I can insert random cards.

DECLARE @startNumber bigint
DECLARE @endNumber bigint
set @startnumber = 2100000199990
set @endnumber = 2100000199999




DECLARE @i bigint
SET @i = @startNumber

DECLARE @card_carNumber nvarchar(50)

WHILE @i <= @endNumber
BEGIN


SET @card_carNumber = CONVERT(nvarchar(50), @i)


IF NOT EXISTS (SELECT * FROM cognetic_members_card WHERE card_cardNumber = CONVERT(nvarchar(50), @i))
    INSERT into cognetic_members_card (card_cardNumber,card_status)
values (CONVERT(nvarchar(50), @i),5)
    SET @i = @i + 1  
END

That will work but as you can see I need a modification. I can probably do so but I have some concerns. I was thinking of a GUID but obviously I would have to cut it down. If I cut it down then I have to compare the inserted cards not to already exist and those cards are already millions, so I will probably block the server. Granted this will be a one off and maybe I can get away with it if I do it on late hours but still I don't think it's a good way.

[Zvoni]

I'm not sure about that range you described.

Your Range is something like
2100009199999 ,
2140000199999,
2103560199999 etc.

Meaning:
"21", followed by 5 digits, followed by "199999"
That's what i read from your description


If i look at your SQL, i get something different

DECLARE @startNumber bigint
DECLARE @endNumber bigint
set @startnumber =     2100000199990 //ENDS on 0 !!!
set @endnumber =       2100000199999  //ENDS on 9

That is something different.

What's correct?

In any case: The compare against existing cards should be doable with a recursive CTE, creating all card-numbers you can, then LEFT JOIN to the existing card-numbers and then eliminate the existing ones with an IS NULL in the WHERE-Clause

Something like Aircode

WITH
   CTE(CTECardNo ) AS (SELECT CTECardNo FROM...... Here the CTE-Code to create all Card-Numbers in specified Range)

SELECT CTECardNo, CardNnumberIssued 
FROM CTE LEFT JOIN SomeTable ON CTECardNo=CardNnumberIssued 
WHERE CardNnumberIssued IS NULL

EDIT: Here's a fiddle
https://dbfiddle.uk/-EdyfzqT

Sample for recursive CTE building 10 CardNumbers acc. to your FIRST pattern

WITH
CTE(CN, CI) AS (
  SELECT CAST('2100000199999' AS VARCHAR) AS CN, 0 AS CI
  UNION ALL
  SELECT CAST(CONCAT('21',Format(CI+1,'00000'),'199999') AS VARCHAR), CI+1 FROM CTE WHERE CI<10
  )
SELECT CN, CI FROM CTE

Returns

CN	CI
2100000199999	0
2100001199999	1
2100002199999	2
2100003199999	3
2100004199999	4
2100005199999	5
2100006199999	6
2100007199999	7
2100008199999	8
2100009199999	9
2100010199999	10

[Zvoni]

I‘m an idiot.
there is no need to cast back and forth to varchar.

Can be replaced with basevalue + (ci+1) * 1 million

tomorrow….

[sapator]

Yes.
Thank cuz I'm a little confused :P
Also the cards should start over 2100010199999 and end at 2199999999999
It might be possible to issue another code so that they will start at 22 and avoid all the trouble but I have to see if the system supports it and that could take a while as I have to get approval, go to the accounting go to the lawyers go to the management etc :P

[Zvoni]

Huh? MSSQL is weird.
Still have to cast the Anchor and the recursive part.

Ah well.... here not as Varchar, but Bigint
https://dbfiddle.uk/y6RJF4eC

WITH
CTE(CN, CI) AS (
  SELECT CAST(2100000199999 AS BIGINT) AS CN, 0 AS CI  
  UNION ALL
  SELECT CAST(2100000199999+(CI+1)*1000000 AS BIGINT),  
  CI+1 FROM CTE WHERE CI<10  /* Replace "10" with your Upper Bound */
  )
SELECT CN, CI FROM CTE

Adjust the factor "1000000" if you want resulting values in a different "range"

like, removing the factor, the first number would be
2100000199999
the second
2100000200000
the third
2100000200001

and so on....

if factor is 100, the last 2 digits stay "99"
factor is 1000, the last 3 digits stay "999"
and so on

[sapator]

Thanks but I fail to see how this is random.
It's an addition of 10 over values.
Also since I want millions if I raise the CI to 1000 I get "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Maybe I did not explain it well.
If I need 10, 4 random cards over 2100010199999 , I would be expecting p.e. 2152010199999, 2160318199999,2105510369279,2166418799991 .
Thanks.

[Zvoni]

Thanks but I fail to see how this is random.
It's an addition of 10 over values.
Also since I want millions if I raise the CI to 1000 I get "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Ah Crap. Forgot that one (i rarely have to deal with MSSQL).
After the last "FROM CTE" add "option (maxrecursion 0)"
"... FROM CTE option (maxrecursion 0)"
maxrecursion 0 is infinite recursion until it hits the break out criterium

Maybe I did not explain it well.
If I need 10, 4 random cards over 2100010199999 , I would be expecting p.e. 2152010199999, 2160318199999,2105510369279,2166418799991 .
Thanks.

Ah... OK. Got you. Can be done with SQL, but you still have the problem, that the "random" CardNo might exist already (as you wrote).

My approach was to generate a "Sequence" of possible CardNumbers, LEFT JOIN them to the existing CardNumbers, and then eliminate the existing ones with an IS NULL in the WHERE-Clause
(see snippet above).
This resulting set would contain all possible CardNumbers which definitely DON'T already exist

Just add a ROW_NUMBER to THAT resultset (ROW_NUMBER would return "1 To Count" of the "remaining" CardNumbers)
and then SELECT from that using RAND-Function in the Range 1 To Count, comparing the result of RAND to ROW_NUMBER to pick the CardNumber

As for RAND-Function: https://www.w3schools.com/sql/func_sqlserver_rand.asp

For performance, it might be an idea to dump the generated CardNumbers into a temp. Table instead of using a CTE

[sapator]

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.

[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);

[sapator]

Hello.

This is very confusing for me especial the ROW_NUMBER function, I always get confused on what it does. So I will have to keep that as a future solution and check it.
Of course you explained it very well but even with that I get confused :P

One thing tho. I don't have a table with unused cards. I will have to manually created the cards. So I have a table than contains cards from 2100000000000 2100000199999 . These are in the DB created (some assigned some not but ready to be used) the card upper to 2100000199999 I have to do an insert.

[Zvoni]

the card upper to 2100000199999 I have to do an insert.

Do you mean "above" (greater than) 2100000199999?
And those would definitly be unused/unassigned?

[sapator]

Yes.
The range would be something like 2100000200000 -- 2199999999999

Of course I can create a temp table put a gozillion cards in there do the left join and then delete the table.

[sapator]

Come to think of it, instead of left joining , doing row functions and stuff.
Is there a way to pick 1.000.000 randoms with a range 2100000200000 -- 2199999999999 and put it in a table?
That would be easier as I can insert from the temp table to the new table and be done with it.

Something like this with rand. (the limit still bugs out if I use 10000)

WITH
CTE(CN, CI) AS (
  SELECT CAST(2100000199999 AS BIGINT) AS CN, 0 AS CI  
  UNION ALL
  SELECT CAST(2100000199999+(CI+1)*1000000 AS BIGINT),  
  CI+1 FROM CTE  WHERE  CI<1000  /* Replace "10" with your Upper Bound */
  ) 
SELECT CN, CI FROM CTE option (maxrecursion 0)

*1000000 AS BIGINT
*random AS BIGINT something like so

EDIT:

Go damn! Searching for an hour!
So this is a start.

select FLOOR(RAND() * (2199999999999- 2100000200000 + 1)) + 2100000200000;

That will generate the range.

Now with the initial query I can get a range, do a loop and put on a tobedeleted table. I can then insert to the live table.

[Zvoni]

Just came out of a hour long meeting, myself, and saw your answer.

Yes, it would have been exactly my approach.

Notabene: As unlikely it might be, you would still have to check if a newly drawn random Cardnumber has been already inserted (say 800,000 numbers ago)

[sapator]

Ye I think the first query covers that

IF NOT EXISTS (SELECT * FROM cognetic_members_card WHERE card_cardNumber = CONVERT(nvarchar(50), @i))
    INSERT into cognetic_members_card (card_cardNumber,card_status)
values (CONVERT(nvarchar(50), @i),5) 
... blah

I can understand the long hours meetings. We usually do teams meetings as most of our partners are scattered around and it's frustrating if you have work at hand. In location meetings are more joyful , we get a snack, full around, eat, do the 1 hour meeting, full around again... And this is coming from meetings of a big Greece company so they are "serious" :P