generate unique numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

generate unique numbers

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-05-01 : 12:05:45

Hi

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
[code]

is there a way to do this without resorting to identity columns

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-01 : 12:13:57

Something like this...

declare @TableA table (Fruits varchar(10))
insert @TableA
          select 'Oranges'
union all select 'Mangoes'
union all select 'Apricots'

select
    convert(varchar(10), getdate(), 12) + 
        right('00000' + cast(row_number() over (order by Fruits desc) as varchar(6)), 6) as my_number,
    Fruits
from @TableA

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-01 : 14:05:41

Is your table static?
In other words once you've created these values do you need to maintain them when rows are inserted (possibly by concurrent inserts)? You can always use an identity column as the basis for a formatted value during presentation.

Be One with the Optimizer
TG

hrishy
Starting Member

47 Posts

Posted - 2008-05-01 : 14:32:39

Hi Tg

Yes my table is static but i need to insert the records from the select query into another table.

Hi Ryan Randall

Thanks a million i dont have words to express my thanks for solving this problem in such a elegant manner.

I appreciate your time TG and Ryan .

regards
Hrishy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources