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

Ravindra reddy

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....?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sanjeeb Lenka13y ago
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 .
0
Iftikar Hussain13y ago
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
0
Sanjeeb Lenka13y ago
check this link

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
0
Jignesh Trivedi13y ago
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.
0