Post reply

Create 10,000 12 digit random numbers - starting with the number 7

  • April 11, 2016 at 7:29 am

    #309788

    Hello all,

    I need to create a set of 10,000 random 12 digit numbers, like a credit card, except these are going to be for gift cards. They need to start with the number 7 though.

    I also need to at the same time insert them into a lookup table to look up against at a later time when we need to generate another 10,000 records - making sure we do not have duplicates.

    Any help is greatly appreciated.

    Cheers,

    ~D

  • April 11, 2016 at 7:40 am

    #1871152

    SELECT TOP 10000 CAST(RAND(CHECKSUM(NEWID())) * 100000000000 + 700000000000 AS bigint)

    FROM sys.all_columns c1

    CROSS JOIN sys.all_columns c2

    Make sure you have a unique or primary key constraint on the column that you insert into, though - I don't think this method is 100% guaranteed to return distinct values.

    John

  • April 11, 2016 at 8:28 am

    #1871165

    Thank you VERY much, John!

  • April 11, 2016 at 8:38 am

    #1871167

    This method will allow you to get the 10,000 duplicates. Check the index option used, which would be needed and can only be used for PKs and UNIQUE indexes.

    CREATE TABLE #RandomNums( RanNum bigint PRIMARY KEY WITH(IGNORE_DUP_KEY = ON));

    SET ROWCOUNT 10000;

    INSERT INTO #RandomNums

    SELECT CAST(RAND(CHECKSUM(NEWID())) * 100000000000 + 700000000000 AS bigint)

    FROM sys.all_columns c1

    CROSS JOIN sys.all_columns c2;

    SET ROWCOUNT 0;

    SELECT COUNT(*), COUNT(DISTINCT RanNum)

    FROM #RandomNums;

    DROP TABLE #RandomNums;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 11, 2016 at 2:42 pm

    #1871275

    John,

    How would I select and check against the table I made and inserted into. I am not sure how to check against a column of sys.all_columns...

    Here is what I have now...

    INSERT INTO Gift_Card_Numbers

    SELECT TOP 10000 CAST(RAND(CHECKSUM(NEWID())) * 100000000000 + 700000000000 AS bigint) as GiftCardNum

    FROM sys.all_columns c1

    CROSS JOIN sys.all_columns c2

    WHERE NOT EXISTS (

    SELECT GiftCardNum

    FROM Gift_Card_Numbers)

  • April 11, 2016 at 2:43 pm

    #1871276

    Thank you, Luis.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply