May 29, 2009 at 11:40 am
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
May 29, 2009 at 12:54 pm
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
May 29, 2009 at 1:08 pm
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
May 29, 2009 at 1:32 pm
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'.
"
May 29, 2009 at 1:37 pm
Thanks dear.... its working now:-)
May 29, 2009 at 1:54 pm
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?;-)
May 29, 2009 at 2:03 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply