Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: generate unique numbers for each record

    Hi

    I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.

    Something like table A has

    Code:
    FRUITS
    -------------
    Oranges
    Mangoes
    Apricots
    ....
    I want to write a sql query that does something like

    SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
    fruits
    from table A

    so the output is

    Code:
    my_number           Fruits
    080501000001       Oranges
    080501000002       Mangoes
    080501000003       Apricots
    ....
    regards
    Hrishy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use an Identity column, and then create a separate calculated column that concatenates it with your date string.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi

    I was wundering if there was a way to do this without resorting to identity column


    regards
    Hrishy

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to do this, but first we need to understand why you want to avoid identity columns (that will make a difference on how to generate your numbers).

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    declare @t table (f1 char(1) not null)
    insert @t
       select 'A' union all
       select 'B' union all
       select 'C' union all
       select 'D'
    select my_number, f1 from (
       select f1, my_number = row_number()
          over(order by f1) from @t
       ) x
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •