[thelist] DB increments without an auto increment

[Steve Cook]

That's a great answer Rudy! I'll try and pick out the relevant questions you
asked.

We're using MS SQL Server 7 as the database. The reason it is legacy is that
we have an old Delphi desktop application that is used as a CRM tool. While
this *could* be updated, for reasons I'd prefer to not go into we are
deciding to leave it as it is. I am now writing an addition to the tool
which will allow us to perform a set of action against groups of customers
in the database and then update a row of log information with an entry for
each customer.

Because we have no in-house experience writing desktop application we are
writing these add-ons as intranet functions using ASP.

The number of updates at a time is probably low for the particular client I
am working with right now but as my boss wants us to be able to offer these
add-ons to other clients we could be talking several hundred up to several
thousand updates at a time.

When I talk about doing inserts in a loop I'm thinking of using two record
sets - one containing the results of a SELECT fetching all the ID numbers
for the records we want to update and another with the log information table
open adding new rows until it is finished. That way I can start by reading
the last ID number and use the loop to increment the inserting ID number.

Of course this method is also in need of a transaction to make sure nothing
goes awry, but  with the right type of locking I'm pretty sure that it will
do the job. It's by no means as efficient as what I had hoped would be
available to do it in SQL, but it should do the job without causing
problems.

Cheers again for the advice.

.steve


----------------------------------
 Cookstour - http://cookstour.org
----------------------------------