Declare @TotalCard Numeric(18,0),@CardCounter Numeric(18,0),@RandomNumber Varchar(20)
Select @TotalCard=100000,@CardCounter=1
DECLARE @Upper Numeric(18,0);
DECLARE @Lower Numeric(18,0)
---- This will create a random number between 1000000000 and 9999999999
SET @Lower = 1000000000 ---- The lowest random number
SET @Upper = 9999999999 ---- The highest random number
--Loop for number of card to be genrated
WHILE(@CardCounter<=@TotalCard)
BEGIN
SELECT @RandomNumber = CAST(CAST(ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) AS Numeric) As Varchar)
IF EXISTS(SELECT 1 FROM tbl_RandomNumber With(Nolock) where RandomNumber=@RandomNumber)
BEGIN
SET @RandomNumber = NULL
END
ELSE IF(LEN(@RandomNumber)<>10)
BEGIN
SET @RandomNumber = NULL
END
ELSE
BEGIN
INSERT INTO tbl_RandomNumber(RandomNumber)
VALUES(@RandomNumber)
END
WHILE(ISNULL(@RandomNumber,'')='')
BEGIN
SELECT @RandomNumber = CAST(CAST(ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) AS Numeric) As Varchar)
IF EXISTS(SELECT 1 FROM tbl_RandomNumberDigit With(Nolock) where RandomNumber=@RandomNumber)
BEGIN
SET @RandomNumber = NULL
END
ELSE IF(LEN(@RandomNumber)<>10)
BEGIN
SET @RandomNumber = NULL
END
ELSE
BEGIN
INSERT INTO tbl_RandomNumberDigit(RandomNumber)
VALUES(@RandomNumber)
END
END
SET @CardCounter = @CardCounter + 1
END -- End of loop