31 May, 2010

Auto Generate AlphaNumeric ID’s in a SQL Server Table

Auto Generate AlphaNumeric ID’s in a SQL Server Table: "
I was recently working on a query to auto generate a Column ID based on an IDENTITY column. I used computed columns to achieve this. If you too have a similar requirement, then here’s the query.
DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)

INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')

SELECT * FROM @TT
OUTPUT
Auto Generate SQL Server Column
Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.

1 comment:

Suggestions are invited from readers