I'm using V3 (not client server) but wish to offer a C/S version of the
software in the future, so I'm trying to do things in a way that will make
it easy for me to make the move later this year.

Okay, so I two similar but not identical tables where I need to occasionally
move completed items from one table to another.  I'd like to use SQL to do
this.

My problem is that it must be done in real time so that no users are blocked
for significant time during the process, I would like to use transactions to
insure integrity, and I'm using op-locking.  Typically, there should be
1,000 records involved in the transfer, but there could be as many as 10,000
in certain circumstances.  Probably never more than that.

My confusion comes with the use of transactions within the SQL script to do
this.  Ideally, if any part of the process fails, I'd like to rollback the
entire process.  However, that would mean a single transaction would have to
lock the affected tables while 10,000 records are transferred which would
surely be too long.

Can anyone suggest the best approach to doing this?

TIA

David

David,

<< My confusion comes with the use of transactions within the SQL script to
do this.  Ideally, if any part of the process fails, I'd like to rollback
the entire process.  However, that would mean a single transaction would
have to lock the affected tables while 10,000 records are transferred which
would surely be too long. >>

10,000 records won't take very long at all, actually.  However, you need to
make sure that the execution of the SQL script is protected with a
try..except block to ensure that the transaction is rolled back if there is
an exception of any kind.  3.x and 4.x don't have any method of trapping
errors within the SQL script itself, so you have to handle it outside in
Delphi code.

--
Tim Young
Elevate Software
www.elevatesoft.com