Set Cust ID as Cust001 with Auto Increment

Question

Set Cust ID as Cust001 with Auto Increment

Gopal Singh

SSC Eights!

Points: 965
More actions
May 29, 2009 at 11:40 am

#210067

How can set Cust_Id as Cust001, Cust002..... and so on when new recored enter in a table every time......
Default Value='Cust001'
When new record is enter in a table, then new value should be increment by 1.
Please help me........
Regards,
Gopal Singh
9936971939

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

IDENTITY comes with gaps in the auto increment so you can use DBCC CHECKIDENT to reseed the IDENTITY property and use SCOPE_IDENTITY to return the last number, the reason is delete, transaction rollback and other operation that did not persist can be rolled back by SQL Server which will create gap in your numbers.

http://msdn.microsoft.com/en-us/library/ms176057.aspx

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Kind regards,
Gift Peddie

GSquared SSC Guru Points: 260824 More actions · Answer 2

Have an identity column, and a calculated column based on it.

create table #T (

ID int identity primary key,

CustomerID as 'Cust' + right('000' + cast(ID as varchar(3)), 3));

insert into #T

default values;

insert into #T

default values;

insert into #T

default values;

select *

from #T;

Does that help?

Edit: Do realize that you can only have 999 customers with something like this. If that's fine, go for it. Otherwise, use something else.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gopal Singh SSC Eights! Points: 965 More actions · Answer 3

i have create the table structure as......

use MMS

create table customer(

id int not null,

fname varchar(50),

lname varchar(50),

cust_add varchar (250),

comments varchar (250),

); id DBCC CHECKIDENT

and i got the error like....."Msg 102, Level 15, State 1, Line 8

Incorrect syntax near 'id'.

"

Gopal Singh SSC Eights! Points: 965 More actions · Answer 4

Gopal Singh

SSC Eights!

Points: 965

May 29, 2009 at 1:37 pm

#1002249

Thanks dear.... its working now:-)

Gopal Singh SSC Eights! Points: 965 More actions · Answer 5

but i need when i insert data through form like name address, cell number , the customer id will increase automatically which is not happen at this time..... any idea?;-)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

Did you create a computed column as GSquared indicated? You can't have an identity that is a character. You have an integer identity and then you have a computed column into which you DO NOT insert data. It is calculated when a new row is added.

Why did you have the DBCC CHECKIDENT? Not needed.