[VIEWED 9792
TIMES]
|
SAVE! for ease of future access.
|
|
The postings in this thread span 2 pages, go to PAGE 1.
This page is only showing last 20 replies
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-04-08 7:10
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
The postings in this thread span 2 pages, go to PAGE 1.
This page is only showing last 20 replies
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 1:13
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
sumansuman
Please log in to subscribe to sumansuman's postings.
Posted on 06-06-08 2:08
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
insert into TableA(CustomerNumber) SELECT dbo. NextCustomerNumber() as CustomerNumber
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-06-08 2:46
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
i guess suman got da right point. if still dont work let us know...
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:01
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Hey guys, ![](../chat/smileys/m-smile.gif) 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?
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:07
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Also, I am getting all the information from: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server as arnzombie suggested.
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:17
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Ok, It works now!! YAY ![](../chat/smileys/m-biggrin.gif) 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
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:22
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
One minor issue is that it starts from C0534 instead of C0000..hmmm
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 3:48
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Sorry guys, I have another problem ![](../chat/smileys/m-sad.gif) 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
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-06-08 4:02
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 4:12
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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? ![](../chat/smileys/m-smile.gif) 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!!
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 4:13
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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? ![](../chat/smileys/m-smile.gif) 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!!
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-06-08 6:35
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-09-08 11:07
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-09-08 11:23
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-09-08 12:59
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-09-08 1:25
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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 ‘1st 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.
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-09-08 2:59
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
Last edited: 09-Jun-08 03:02 PM
Last edited: 09-Jun-08 03:04 PM
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-09-08 3:00
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
duplicate post
Last edited: 09-Jun-08 03:03 PM
|
|
|
U?Me
Please log in to subscribe to U?Me's postings.
Posted on 06-09-08 5:15
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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?
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-10-08 8:35
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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;
|
|