By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,876 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,876 IT Pros & Developers. It's quick & easy.

Build ID w/Incrementing Digit

P: n/a
Help, I'm stuck! I have a Customer table:

ID Name
-- ---------------------
1234 Christopher's Surf Boards
4321 Christina's Nails

I have to build a Account table:
ID GPID
-- --------------------
1234 CHRIST00
4321 CHRIST01

I've built a function that gives me the six alphas and concatenates the
two digits. The GPID in the account table is the first six alphas of
the company name plus two digits to ensure uniqueness. (Don't ask why I
can't use the existing id -- the story is long and boring.) I built a
function that creates the alpha part and assigns the first 00. I built
a second function that I thought would check the Account table as I was
inserting and increment the numbers if the account number already
existed. It doesn't work. In the above table, both IDs come out as
CHRIST00. Here's the second function:

ALTER FUNCTION fnValidateID
(
@mString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN
DECLARE
@sTemp varchar(15),
@sInc varchar(2),
@iInc int
BEGIN
set @sTemp = @mString
while exists (select GPID from dbo.tempID where GPID = @sTemp)
begin
set @iInc = convert(int,right(@mString,2)) + 1
set @sInc = convert(varchar(2),@iInc)
if len(@sInc) < 2 set @sInc = '0' + @sInc
set @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sInc
end
--NEXT
END
RETURN @sTemp
END
My first guess is that the code is okay, so far as it goes, but that I
can't check the values in the table at the same time I'm inserting into
it. (The Account table is empty, initially.) In any case, I can't
figure out where I've gone wrong.
Thanks.

Jul 23 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On 2/12/05 7:20 PM, in article
11**********************@z14g2000cwz.googlegroups. com, "EoRaptor013"
<rc*******@patmedia.net> wrote:
Help, I'm stuck! I have a Customer table:

ID Name
-- ---------------------
1234 Christopher's Surf Boards
4321 Christina's Nails

I have to build a Account table:
ID GPID
-- --------------------
1234 CHRIST00
4321 CHRIST01

I've built a function that gives me the six alphas and concatenates the
two digits. The GPID in the account table is the first six alphas of
the company name plus two digits to ensure uniqueness. (Don't ask why I
can't use the existing id -- the story is long and boring.) I built a
function that creates the alpha part and assigns the first 00. I built
a second function that I thought would check the Account table as I was
inserting and increment the numbers if the account number already
existed. It doesn't work. In the above table, both IDs come out as
CHRIST00. Here's the second function:

ALTER FUNCTION fnValidateID
(
@mString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN
DECLARE
@sTemp varchar(15),
@sInc varchar(2),
@iInc int
BEGIN
set @sTemp = @mString
while exists (select GPID from dbo.tempID where GPID = @sTemp)
begin
set @iInc = convert(int,right(@mString,2)) + 1
set @sInc = convert(varchar(2),@iInc)
if len(@sInc) < 2 set @sInc = '0' + @sInc
set @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sInc
end
--NEXT
END
RETURN @sTemp
END
My first guess is that the code is okay, so far as it goes, but that I
can't check the values in the table at the same time I'm inserting into
it. (The Account table is empty, initially.) In any case, I can't
figure out where I've gone wrong.
Thanks.


In a similar situation, I handled the query by setting the value MAX() + 1.
So, perhaps creating the GPID on the fly... MAX(RIGHT(GPID,2))+1 WHERE
LEFT(GPID,6) = @mString

-Greg

Jul 23 '05 #2

P: n/a
Gregory Dean wrote:

In a similar situation, I handled the query by setting the value MAX() + 1. So, perhaps creating the GPID on the fly... MAX(RIGHT(GPID,2))+1 WHERE LEFT(GPID,6) = @mString

Thanks! This is certainly getting me closer. The problem remains,
however, that I'm only getting the first increment. In other words, if
CHRIS00 exists, I'll get CHRIS001, but if a third CHRIS comes along in
the same query it gets the CHRIS01. From my vauge understanding of
transactions, I would say that the newly created CHRIS01 hasn't been
committed and, therefore, isn't there to be incrmented in the same
batch.
Any thoughts on this?
Thanks again.
Randy

Jul 23 '05 #3

P: n/a
Actually, the more I think about it, the more likely this seems.
Starting earlier in the process, I'm doing a select from the original
customer table:

INSERT into Account
select
distinct c.id,
dbo.fnMakeCID(c.Name)
from
company as C

(fnValidateID is called from fnMakeID)
Some how, I would have to commit after each inserted record before that
record would be available to increment. I know I'm really putting my
ignorance on display here but as I read the Books Online, and other
documentation, SQL commits after my query has completed, not as each
insert is completed.
If all this is the case, how do I get around it?
Thanks

Jul 23 '05 #4

P: n/a
On 2/13/05 11:54 AM, in article
11**********************@o13g2000cwo.googlegroups. com, "EoRaptor013"
<rc*******@patmedia.net> wrote:
Actually, the more I think about it, the more likely this seems.
Starting earlier in the process, I'm doing a select from the original
customer table:

INSERT into Account
select
distinct c.id,
dbo.fnMakeCID(c.Name)
from
company as C

(fnValidateID is called from fnMakeID)
Some how, I would have to commit after each inserted record before that
record would be available to increment. I know I'm really putting my
ignorance on display here but as I read the Books Online, and other
documentation, SQL commits after my query has completed, not as each
insert is completed.
If all this is the case, how do I get around it?
Thanks


Maybe this would be a good place for an INSERT trigger on the company table.
Whenever a new record is inserted into the company table a trigger is fired
that inserts a matching record (using the MAX() + 1 theory) into the
accounts table. Let the server do the work.

Jul 23 '05 #5

P: n/a
On 12 Feb 2005 16:20:19 -0800, EoRaptor013 wrote:
Help, I'm stuck! I have a Customer table:

ID Name
-- ---------------------
1234 Christopher's Surf Boards
4321 Christina's Nails

I have to build a Account table:
ID GPID
-- --------------------
1234 CHRIST00
4321 CHRIST01

I've built a function that gives me the six alphas and concatenates the
two digits. The GPID in the account table is the first six alphas of
the company name plus two digits to ensure uniqueness. (Don't ask why I
can't use the existing id -- the story is long and boring.) I built a
function that creates the alpha part and assigns the first 00. I built
a second function that I thought would check the Account table as I was
inserting and increment the numbers if the account number already
existed. It doesn't work. In the above table, both IDs come out as
CHRIST00. Here's the second function: (snip)My first guess is that the code is okay, so far as it goes, but that I
can't check the values in the table at the same time I'm inserting into
it. (The Account table is empty, initially.) In any case, I can't
figure out where I've gone wrong.


Hi EoRaptor013,

Your guess is right. Checking the rows already in the table would be fine
if you were inserting rows one at a time, but that would kill the
performance of your conversion.

I'll assume that to you, it doesn't matter which of these customers gets
ID CHRIST00 and which one gets CHRIST01 - but for SQL Server, you should
specify some criteria. There is no "pick any order you like" operator in
SQL. So I'll just pick one, and say that customers with the same six
starting characters in the name will get their extra digits in order of
cliombing ID number(I assume that is unique).

SELECT a.ID, a.Name,
UPPER(LEFT(a.Name,6))
+ RIGHT('0'
+ CAST((SELECT COUNT(*)
FROM Customers AS b
WHERE UPPER(LEFT(b.Name,6)) = UPPER(LEFT(a.Name,6))
AND b.ID < a.ID) AS varchar(2)), 2)
FROM Customers AS a
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #6

P: n/a
Gregory Dean (gd***@datapex.com) writes:
Maybe this would be a good place for an INSERT trigger on the company
table. Whenever a new record is inserted into the company table a
trigger is fired that inserts a matching record (using the MAX() + 1
theory) into the accounts table. Let the server do the work.


But that would also only work if one row at a time is inserted. EoRaptor013
appears to be inserting all rows at once.

The code that Hugo offered looks good, for the first load. For subsequent
loads the next CHRIST should be 03 or whatever, so the code would have to
look in bother places.

....and then there is the interesting case when you have CHRIST99 and the
next CRHIST comes along...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

P: n/a
If CHRIST comes again, a lot of people think we will have other things
to worry about! (Sorry, it's late and I couldn't help myself).

Jul 23 '05 #8

P: n/a
Thank you all for the insights and suggestions. I learned a lot from
them that will help me many times in the future. Unfortunately, through
my own ignorance, I couldn't get Hugo Kornelis's solution to work and I
couldn't figure out a way to make a trigger work. In the end, I
resorted to a cursor which did the trick for me. Here's what I ended
up with:
set concat_null_yields_null off
go
declare cpid cursor for
select
distinct c.replicaid,
dbr.dbtitle
from
company as c join db_rep_id as dbr on c.replicaid = dbr.serverrepid
declare
@repid char(16),
@dbTitle varchar(64)
open cpid
fetch cpid into @repid,@dbtitle
while @@fetch_status = 0
begin
begin transaction
insert into parentID
(replicaid,dbtitle,parentid)
values
(@repid,@dbtitle,dbo.fnMakeAlpha(@dbtitle,2))
commit
fetch next from cpid into @repid,@dbtitle
end
close cpid
deallocate cpid

I only had 450 records so it only took about 3 seconds. I have another
table that will result in about 17,000 records; can't wait to see how
long THAT takes.

Perhaps my code here will give a better idea of what I was trying to
accomplish. I know cursors are frowned upon but it was the only thing I
could get to work.

Again, thanks to all. It's been quite an education!

Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.