Hi All:
I have a column in a table with datatype varchar(60). I need to start inserting values into this column (starting sequentially from R000001 onwards). So, the next record would be R000002, so on and so forth. I tried using NewID() function, however this generates unique hexadecimal numbers like 019D9F4F-E16C-4EDF-9E02-DF1190B7AECF

Any help is greatly appreciated

This is what I did:

create function NextCustomerNumber()
returns char(7)
as
begin
    declare @lastval char(7)
    set @lastval = (select max(CustomerNumber) from absences)
    if @lastval is null set @lastval = 'C000001'
    declare @i int
    set @i = right(@lastval,4) + 1
    return 'C' + right('000000' + convert(varchar(10),@i),4)
end

THEN,
insert into TableA(CustomerNumber)
SELECT NextCustomerNumber()  as CustomerNumber

BUT this gives me error

Incorrect syntax near the keyword 'SELECT'.

'NextCustomerNumber' is not a recognized function name.

insert into TableA(CustomerNumber)
SELECT dbo. NextCustomerNumber()  as CustomerNumber

i guess suman got da right point.  if still dont work let us know...

Hey guys,
Thanks for the prompt help. Now I was able to insert values, but they were all the same(C000001) for the columns that were inserted since my query does multiple inserts satisfying a condition. But I want values to be inserted as C000001, C000002, and so on. So what I did, was create a Table B, with

CustomerID varchar(60) ---same datatype as TableA
dbID int identity not null primary key,

Then insert into TableB(CustomerID)
select CustomerID........

Now I am trying to insert these values from TableB to TableA,
and i get this error

Msg 512, Level 16, State 1, Procedure NextCustomerNumber, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any ideas?

Also,
I am getting all the information from:

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

as arnzombie suggested.

Ok,
It works now!! YAY

alter function NextCustomerNumber (@id int)
returns char(5)
as
begin
return 'C' + right('0000' + convert(varchar(10), @id), 4)
end

Then,
select
...
dbo.NextCustomerNumber(dbID) as CustomerID,
...

Thanks to all of you!! YOU GUYS ROCK

One minor issue is that it starts from

C0534 instead of C0000..hmmm

Sorry guys,
I have another problem

I have to do insertion once a week.

When I try to insert (multiple data) more than once, the CustomerId gets repeated.
Example, the first time I inserted it, the values were C0534.....C0573
Now again, when I insert another block of data, the same set of values for CustomerId get repeated.

Any help is greatly appreciated

again proble??

just kidding dude . actually am going out of desk now.going home now. i will see once i get into appt.

Rawbee,
I know right! I solved the problem.......And btw im a girl errmmmmm...lady actually so be nice j/k.

Everyone,
I am not an expert in SQL. I know it well but everyone runs into a new problem, right?

Another problem, I am trying to
insert into another live Table C, a value CompanyCode. When I do,

select
Customer.company_code

from Table B
left outer join TableC Customer on......

where ....
I get this error:
Msg 2601, Level 14, State 3, Procedure InsertCicmpy1stNotice, Line 11
Cannot insert duplicate key row in object 'Table C with unique index 'AccountCode'.
The statement has been terminated.

I dont know where this AccountCode is from?? Thanks!!

Rawbee,
I know right! I solved the problem.......And btw im a girl errmmmmm...lady actually so be nice j/k.

Everyone,
I am not an expert in SQL. I know it well but everyone runs into a new problem, right?

Another problem, I am trying to
insert into another live Table C, a value CompanyCode. When I do,

select
Customer.company_code

from Table B
left outer join TableC Customer on......

where ....
I get this error:
Msg 2601, Level 14, State 3, Procedure InsertCicmpy1stNotice, Line 11
Cannot insert duplicate key row in object 'Table C with unique index 'AccountCode'.
The statement has been terminated.

I dont know where this AccountCode is from?? Thanks!!

^ ^

Maybe 'Accountcode' is an Index ........check index and make twist as needed..

tips: disable the index and see if it works...just to make sure its an index problem and take it from there.

Good Luck

Hi,

Yes, AccountCode is a unique non-clustered index:

 

When I insert the data into Live Table A, all other fields but this(Represented by Account) will be displayed o because it is derived from this unique non-clustered index.

I tried to do an insertion on Table B (where the Account # is), but I got this error:

 

Msg 2601, Level 14, State 3, Line 1

Cannot insert duplicate key row in object ''B'with unique index 'AccountCode'.

The statement has been terminated.

 

(0 row(s) affected)

 

Now, There is a column in both Table A and Table B. Insertion in Table A works, but how do I update that value in Table B.?

Thanks in Advance!

 

First, Do you really want duplicate values on Table B. If yes why do  you have unique index. Simply drop the index and you should be fine.

Secondly, Wat kinda value do you want in Table B. Are you trying to insert from table A.

Can you be more specific on the value you want on table B and why are you inserting duplicate values and how do you want that to be handled.

Ok here is the deal.
There is this intranet website.

I basically have to create an automatic process of sending out the Renewal Notices when the customer’s subscription is about to expire.


There is a live table A with Customer Account information. There is another live Table B with customer shipping, ID, etc.  I had to gather a list of customers whose subscription ends in a certain date range and change their NoticeStatus to '1stNotice'...etc

 

I had to create a table C with all CustomerAccountInfo (from live table A) where subscription ends in 2 months from now. After that I update their renewal status to ‘1^st Notice’ .

 

Then on the main Live table A , I append all these records I just generated from table C, also doing case when ….statements.

Now, all the fields in the intranet gets uploaded automatically, besides one field Subscriber.

This subscriber field is apparently generated from unique non-clustered index in live Table B. I cannot change the structure of that table.

Table B holds CustomerPersonalInformation including a column cmp_code is the company code which needs to be put in the field Subscriber.

 

So after I insert into Table A, I tried updating Table B, using :

update cicmpy –this is the table B in my description

set cmp_code = (

select

enduser.cmp_code as Subscriber

 

from

RenewalLetter r –this is the table C in my description

left outer join cicmpy Subscriber on freeguidfield_01 = Subscriber.cmp_wwn

left outer join cicmpy Enduser on CustomerID = Enduser.cmp_wwn

 

where

type = 75

and ltrim(freetextfield_10) = 'Active'

and freedatefield_01 between

dateadd(mm,2,getdate())

AND dateadd(mm,2,getdate())+7

 

and freeboolfield_05 not like '1'

and subscriber.classificationid in ( 'HMO', 'EUR'))

 

And I get this error I explained in the previous post.

Certainly Its a very very complicated select statment,..

Wondering if it is possible to avoid the outer join with cicmpy enduser after the outerjoin between renewlletter and the cicmpy subscriber...it gets to complicated.

Also, i bet you have check the result of your select stattement.. can you check it again and make sure that the select statement is giving you more than one null in result. As more than one Null would violate the unique constraint.

Try doing this in your test server and see if it works

CREATE UNIQUE INDEX accountcode ON cicmpy (subscriber)
    WITH (IGNORE_DUP_KEY = ON);

PS: Remember its Monday, stressful day for most of us.

duplicate post

Hey Arnzombie ,
Thanks for your continuous help. I called a meeting to try to understand and solve this issue. On the other hand, let's go back to the original problem I posted (hence the start of this thread). I created this function:

alter function NextCustomerNumber (@id int)
returns char(7)
as
begin
return 'R' + right('000000' + convert(varchar(10), @id), 6)
end

Now, the problem I have is, the number starts wherever it pleases. Instead of starting at C00001, and auto-incrementing by 1, this is just too random. any suggestions?

Its my pleasure, anyways why dnt  you get the highest id number in this case or pass the highest id number as parameter...

alter function NextCustomerNumber

returns char(7)
as
begin

set @id = select max(ID) from table_name ;--to get the highest id number from the table
return 'R' + right('000000' + convert(varchar(10), @id), 6)
end;

----------------------------------------------------------------------------------------------

OR

begin

set @id = select max(ID) from table_name ;--

select NextCustomerNumber(@id) ....

END;