CODEWITHSUNDEEP
sql-serverstored-procedures
chamara
chamara

Reputation: 12711

auto increment custom ID in database table

I have a EMPLOYEE table with EMP_ID,EMP_NAME,EMP_ADDRESS. EMP_ID should have the following format.

EMP001
EMP002
EMP003
......

I also need to use EMP_ID as the primary key and should be auto generated is it possible?

Upvotes: 1

Views: 3785

Answers (1)

gbn
gbn

Reputation: 432210

Use an IDENTITY and a computed column?

CREATE TABLE EMPLOYEE (
     RealID int NOT NULL IDENTITY (1, 1),
     EMP_NAME ...
     ...
     /*gives 000-999. Change the RIGHT as needed to give more*/
     EMP_ID AS 'EMP' + RIGHT('000000000' + CAST(RealID as varchar(10)), 3)

     CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED (EMP_ID)
     )

You can change the RIGHT to cover as many digits as needed, or you may not want leading zeroes:

     EMP_ID AS 'EMP' + CAST(RealID as varchar(10))

Upvotes: 4

Related Questions