Insert random numbers into a column

  • I have a table, one of the columns is empty. I want to insert a random number into each row of the empty column, under the condition that no two rows have the same number. Can someone show me how? Thanks.

  • In order for someone to assist you will you please supply the table definition and indexes and a short sample of the data contained in the other columns. To learn how to provide this information quickly and easily please click on the first link in my signature block.

    For the column which is to contain the random values be sure to specify the data type, i.e., INT, BIGINT, DECIMAL, FLOAT .. etc.

    Other than that have you examined the RAND function available in SQL ?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • henryvuong1000 (3/20/2011)


    I have a table, one of the columns is empty. I want to insert a random number into each row of the empty column, under the condition that no two rows have the same number. Can someone show me how? Thanks.

    First, since you're mostly a newbie on this forum, please study and practice the first link in my signature below to get coded answers much more quickly.

    Shifting gears, since you didn't provide any data, column names, nor even a table name, I made my own to suit the purposes of this demo. 🙂 Please read the comments so you know what is going on here. 😉

    /****************************************************************************************

    Purpose:

    Create a voluminous test table with various types of highly randomized data.

    --Jeff Moden

    ****************************************************************************************/

    --===== Conditionally drop the test table to make reruns easier

    IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL

    DROP TABLE dbo.JBMTest

    ;

    --===== Create and populate a 1,000,000 row test table.

    -- "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

    -- "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

    -- "SomeNRRand" will become a Non-Repeating Random number

    -- This will take something less than 11 seconds

    SELECT TOP 1000000

    SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

    SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

    SomeHex12 = RIGHT(NEWID(),12),

    SomeNRRand = CAST(NULL AS INT)

    INTO dbo.JBMTest

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    As you can see, the SomeNRRand column has nothing but NULLs in it.

    Enter the nearly magical power of CTE's; This UPDATE uses a CTE to create a randomly ordered Row Number and associate it with each row in the table without any kind of join. Like I said, it's magic. The UPDATE statement simply applies the calculated column called MyRand and puts it into SomeNRRand in the table itself. The reference to NEWID() is what makes it "random". It's the only multi-row random number in all of SQL Server.

    WITH -- This will take something less than 22 seconds because it has to sort

    cteRand AS

    (

    SELECT MyRand = ROW_NUMBER() OVER (ORDER BY NEWID()),

    SomeNRRand

    FROM dbo.JBMTest

    )

    UPDATE cteRand

    SET SomeNrRand = MyRand

    ;

    Of course, we should verify that it actually worked...

    --===== This just double checks to make sure that no two

    -- random numbers are alike.

    SELECT SomeNRRand, COUNT(*)

    FROM dbo.JBMTEST

    GROUP BY SomeNRRand

    HAVING COUNT(*) > 1

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Craig Farrell (3/20/2011)


    This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.

    But usually consecutive in sequence (that is when rows have not been deleted from the table) and hence I would not consider that as truly random .. but then again who knows what the OP really wants/needs unless he/she is more specific.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (3/20/2011)


    Craig Farrell (3/20/2011)


    This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.

    But usually consecutive in sequence (that is when rows have not been deleted from the table) and hence I would not consider that as truly random .. but then again who knows what the OP really wants/needs unless he/she is more specific.

    True. It was the 'unique' bit that caught my eye on that one. I suppose you could ABS() a NewID cast to bigint and run that multiple times for null entries against a unique index on the column, but that seemed kinda overkill to me. Jeff's solution above randomizes who gets the ID field, and is neat, but it's still just an identity() column in an odd order.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • bitbucket-25253 (3/20/2011)


    Craig Farrell (3/20/2011)


    This seems exactly like why Identity() was built. It's random in that it has nothing to do with the rest of the data, and is unique per table.

    But usually consecutive in sequence (that is when rows have not been deleted from the table) and hence I would not consider that as truly random .. but then again who knows what the OP really wants/needs unless he/she is more specific.

    Hi Ron,

    If you look at what I've done, it's no different than what Craig suggests. The numeric assignment made be an IDENTITY in sequential order isn't really any different that the assignment of numbers in a random order and sequences are the easiest way to guarantee no duplication of the "random" number.

    The proof of the pudding would be to sort a table with an IDENTITY column by any other column and the IDENTITY will, indeed, appear to be random unless the sort is done on a date column which indicates the order of entry.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Craig Farrell (3/20/2011)


    but it's still just an identity() column in an odd order.

    I absolutely agree except for one thing... my solution isn't self-maintaining. An IDENTITY column is and, therefor, is also the better solution in this case.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

  • Rao.V (3/21/2011)


    you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

    Do you mean Identity (which has already been suggested) or Sequence which is a new feature in Denali (the next version of SQL Server)?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yes i mean identity

  • Rao.V (3/21/2011)


    you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

    Rao.V (3/21/2011)


    yes i mean identity

    ???:blink: 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/21/2011)


    Rao.V (3/21/2011)


    you also can use option called "SEQUENCE" for that column so that it generates number automatically when u insert a new row

    Rao.V (3/21/2011)


    yes i mean identity

    ???:blink: 😛

    A rose by any other name.....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Heh... but Tiger Lilly just isn't a Rose. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

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