Generate Auto ID Year Wise - 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 2000 Forums

SQL Server Development (2000)

Generate Auto ID Year Wise

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-07 : 16:18:01

Hi all,

I want to generate automated ID in a MS SQL 2000 table, but the ID should be generated year wise, e.g.
For the year 2009:
student_ID: S090001, S090002, S090003,...., S099999

For the year 2010:
student_ID: S100001, S100002, S100003,...., S109999

Hope you understand, what I want to say!
Please HELP!

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 00:44:04

See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 01:32:30

just make a identity column & calculated column based on that. something like

CREATE TABLE test
(ID int identity(1,1),
student_ID AS 'S' + RIGHT(DATENAME(yy,GETDATE()),2) +RIGHT('0000'+CAST(ID AS varchar(4)),4)
)

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-08 : 02:17:23

quote:
Originally posted by visakh16

just make a identity column & calculated column based on that. something like

CREATE TABLE test
(ID int identity(1,1),
student_ID AS 'S' + RIGHT(DATENAME(yy,GETDATE()),2) +RIGHT('0000'+CAST(ID AS varchar(4)),4)
)

Now, when am trying to creat relationship between student_ID column in test and student_ID column in test2, it's giving error that no contraint or primary key is not declared. And also I can't declare this student_ID as a primary key, why so?
What should I do?

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 04:01:29

The computed column must be defined as a primary key if you want foreign key relationship.

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-08 : 05:07:29

quote:
Originally posted by Peso

The computed column must be defined as a primary key if you want foreign key relationship.

Am trying, but student_ID column cannot be defined as primary key...

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 06:01:36

Have you made it persisted?

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-08 : 08:48:41

quote:
Originally posted by Peso

Have you made it persisted?

I think "persisting" exist in MS SQL 2005, but am going to use MS SQL 2000. So there is no option of using persisting in here.

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 12:44:59

Then there are very few options left for you.

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-08 : 14:12:28

quote:
Originally posted by Peso

Then there are very few options left for you.

What are the few options??

Daipayan

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-09 : 14:21:10

HELP! Please help me!
I cannot link the student_ID column with other column in other tables.....PLEASE HELP!!

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 14:42:50

You can use a technique like this
http://weblogs.sqlteam.com/peterl/archive/2008/11/28/An-alternative-to-IDENTITY-column.aspx

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-10 : 09:10:37

Thanks to all the peoples who help me in this query, thankz a lot!

You people just ROCK!
HATS OFF TO ALL OF YOU GUYS!

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources