I want to start auto incrment number start emp001,emp002...

Question

identity column will work only for integer values. Suppose if I want to set id value starts from 08U13000 and increase that value based on new records inserted in table like 08U13000 , 08U13001 , 08U13002 , etc…or emp01,emp02,emp03.......... please give me the complete coding....?

Sanjeeb Lenka · Answer

hi you can do like this create your table like this: CREATE TABLE #tm(id INT NOT NULL IDENTITY(1,1),col AS '08U13'+RIGHT('000'+CAST(id AS VARCHAR(3)),3)) INSERT INTO #tm DEFAULT VALUES SELECT * FROM #tm for checking i created a temp table and inserting default value . you can create your table like above .

Iftikar Hussain · Answer

Hi, Here it is DECLARE @TempEmpId varchar(20) DECLARE @Id INT SET @TempEmpId='08U13000' IF EXISTS(SELECT TOP 1 EmpId FROM Employee WITH(NOLOCK)) BEGIN SELECT TOP 1 @Id=(SUBSTRING(EmpId,2,LEN(UserId)-1)) FROM Employee WITH(NOLOCK) ORDER BY SUBSTRING(EmpId,4,LEN(EmpId)-1) DESC SET @Id=@Id+1 SET @TempEmpId='08U' + CAST(@Id AS VARCHAR) END SELECT @TempEmpId Regards, Iftikar

Sanjeeb Lenka · Answer

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

Jignesh Trivedi · Answer

hi, Please refer following forum in c# corner http://beta.c-sharpcorner.com/Forums/Thread/163919/hi-how-to-auto-increment-for-text-and-char-in-sql-table.aspx it might resolve your problem.

I want to start auto incrment number start emp001,emp002...

Answers (4)