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 2000 Forums
 SQL Server Development (2000)
 how to generate 5 digit series

Author  Topic 

nisar
Starting Member

29 Posts

Posted - 2008-08-25 : 03:08:55
Hi,

I want to generate and auto series
and want to fill a specific table
which has two colums

like

oelid and autoid

I will specify autoid as identity and and seed by 1
but specific format for oelid is

oelid ------------autoid
00001 ------------ 1
00002 ------------ 2


upto

99999------------


Regards
Nisar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 03:12:48
create oelid as computed column

create table yourtable
(
autoid int identity(1,1),
oelid as right('00000' + convert(varchar(5), autoid), 5),
. . .
)



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

nisar
Starting Member

29 Posts

Posted - 2008-08-25 : 03:19:30
hi thanks for your replay
but then how i m going to fill this
table with 99999 records
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 03:31:06
make use of F_TALBE_NUMBER_RANGE http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

insert into yourtable(col)
select NUMBER
from F_TABLE_NUMBER_RANGE(1, 99999)



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

nisar
Starting Member

29 Posts

Posted - 2008-08-25 : 04:06:23
Thanks a lot it works fine but only
one problem is remaining that
all record are inserted like this

autoid ====== oelid
1============= 1
2==============2

and I want

autoid ====== oelid
1============00001

previous solution will work for me but
it will give me problem when i have to
start from beginning i have to generate
5 digits and in that case i will have
only one digit

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 04:10:54
Since oelid is basically autoid prefix with 0, why not create oelid as computed column as i have shown ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 04:12:17
if not just convert to string in the select statement


select NUMBER as autoid, right('00000' + convert(varchar(5), NUMBER), 5) as oelid
from F_TABLE_NUMBER_RANGE(1, 99999)



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 04:13:20
or alternatively don't keep the oelid in the table. During select, format it in your SELECT query or in your front end application.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

nisar
Starting Member

29 Posts

Posted - 2008-08-25 : 04:26:13
Great job boss thanks a lot
Go to Top of Page
   

- Advertisement -