primary key (char) automatically increases

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)

primary key (char) automatically increases

Author

Topic

vuimotti87
Starting Member

1 Post

Posted - 2010-05-21 : 11:18:02

Hi All !
Often when creating a table for its own primary key to growth but that it is the type of case studies. Now if the key types of characters you want it up, how?
Example:
CREATE TABLE Student(
StudCode varchar (5),
Birth date)
I want each key increases with sample A0001, A0002 ... They must do.
Who can help me many thanks!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 12:03:35

Have a look at the below link. It may of some help to you.

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-21 : 12:14:25

IF OBJECT_ID('tempdb..#tbl1') IS NOT NULL DROP TABLE #tbl1;

CREATE table #tbl1
(
TCODE AS CHAR(65 + ID / 10000) + RIGHT('0000' + CONVERT(VARCHAR(5),ID),4) PERSISTED NOT NULL PRIMARY KEY,
ID INT IDENTITY(0,1) NOT NULL,
Field1 INT,
Field2 varchar(10)
);
GO
INSERT INTO #tbl1 (Field1,Field2) SELECT 1,'AAA'
GO 30001

select * FROM #tbl1 order by ID

DROP TABLE #tbl1;

/*
Results:
TCODE ID Field1 Field2
A0000 0 1 AAA
A0001 1 1 AAA
A0002 2 1 AAA
A0003 3 1 AAA
A0004 4 1 AAA
A0005 5 1 AAA
A0006 6 1 AAA
A0007 7 1 AAA
A0008 8 1 AAA
A0009 9 1 AAA
...
A9996 9996 1 AAA
A9997 9997 1 AAA
A9998 9998 1 AAA
A9999 9999 1 AAA
B0000 10000 1 AAA
B0001 10001 1 AAA
B0002 10002 1 AAA
B0003 10003 1 AAA
...
B9997 19997 1 AAA
B9998 19998 1 AAA
B9999 19999 1 AAA
C0000 20000 1 AAA
C0001 20001 1 AAA
C0002 20002 1 AAA
...
C9997 29997 1 AAA
C9998 29998 1 AAA
C9999 29999 1 AAA
D0000 30000 1 AAA

*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources