Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Built table problem

Hi.

I am a beginner of SQL server.
I have a table namely Customer with a column "CustomerName" with data type varchar and length 50.
Column Name-Customer Name
Data Type- varchar
Length- 50.

May I know how to ENSURE that every customer's name only contain ALPHABET and strictly avoid the numeric character and
other characters such as "&, !,#...".

Please help. Thank you.See the BOL for Check Constraints !!!|||What about this idea?

create table test(id int,
code varchar(50)
CONSTRAINT mycheck check(code+replicate('Z',50-datalength(code)) like replicate('[A-Z]',50) or
code+replicate('Z',50-datalength(code)) like replicate('[a-z]',50)
))|||Very Nice

INSERT INTO test(code) SELECT 'ABC'
INSERT INTO test(code) SELECT '123'
INSERT INTO test(code) SELECT 'AB.'|||INSERT INTO test(code) SELECT 'Brett Kaiser'|||Originally posted by Enigma

INSERT INTO test(code) SELECT 'Brett Kaiser'



Now it is even more simple:

drop table test
go
create table test(id int,
code varchar(50)
CONSTRAINT mycheck check(lower(replace(code,' ','Z')+replicate('Z',50-datalength(code))) like replicate('[a-z]',50)
)
)
go
INSERT INTO test(code) SELECT 'Brett Kaiser'|||That looks cool !!! :)

Build ID w/Incrementing Digit

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.On 2/12/05 7:20 PM, in article
1108254019.849688.277530@.z14g2000cwz.googlegroups. com, "EoRaptor013"
<rchrismon@.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|||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|||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|||On 2/13/05 11:54 AM, in article
1108313683.027697.196460@.o13g2000cwo.googlegroups. com, "EoRaptor013"
<rchrismon@.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.|||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)|||Gregory Dean (gdean@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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).|||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!