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 columslike oelid and autoid I will specify autoid as identity and and seed by 1but specific format for oelid is oelid ------------autoid00001 ------------ 100002 ------------ 2upto99999------------RegardsNisar |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-25 : 03:12:48
|
create oelid as computed columncreate table yourtable( autoid int identity(1,1), oelid as right('00000' + convert(varchar(5), autoid), 5), . . . ) KH[spoiler]Time is always against us[/spoiler] |
|
|
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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
nisar
Starting Member
29 Posts |
Posted - 2008-08-25 : 04:06:23
|
Thanks a lot it works fine but onlyone problem is remaining that all record are inserted like this autoid ====== oelid1============= 12==============2and I want autoid ====== oelid1============00001previous solution will work for me but it will give me problem when i have to start from beginning i have to generate5 digits and in that case i will haveonly one digit |
|
|
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] |
|
|
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 oelidfrom F_TABLE_NUMBER_RANGE(1, 99999) KH[spoiler]Time is always against us[/spoiler] |
|
|
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] |
|
|
nisar
Starting Member
29 Posts |
Posted - 2008-08-25 : 04:26:13
|
Great job boss thanks a lot |
|
|
|