At 01:18 AM 27/02/2003 -0800, you wrote:

>I have two databases with identical structures, but different data.
>
>I want to make DatabaseB's TableABC exactly the same as DatabaseA's
>TableABC, with one exception: If any particular key (FieldABC) previously
>exists in DatabaseB's version of the table, I want to preserve the value in
>FieldXYZ. I also want to get rid of any keys in DatabaseB's version of the
>table that don't exist in DatabaseA's version.
>
>Example: (each grouping of 3 fields is a row in TableABC)
>
>DatatbaseA
>================
>
>FieldABC = 123
>FieldDEF = 456
>FieldXYX = 789
>
>FieldABC = 1234
>FieldDEF = 4567
>FieldXYX = 7890
>
>FieldABC = 12345
>FieldDEF = 45678
>FieldXYX = 78901
>
>
>
>Database B (BEFORE)
>==================
>
>FieldABC = 1234
>FieldDEF = 9999
>FieldXYX = 10000
>
>FieldABC = 9999
>FieldDEF = 4567
>FieldXYX = 10001
>
>Database B (AFTER)
>================
>
>FieldABC = 123
>FieldDEF = 456
>FieldXYX = 789
>
>FieldABC = 1234
>FieldDEF = 4567
>FieldXYX = 10000
>
>FieldABC = 12345
>FieldDEF = 45678
>FieldXYX = 78901
>
>As you can see, DatabaseB's version of the table becomes exactly the same
>as DatabaseA's, except that in the one row that already existed, DatabaseB,
>FieldXYZ retained it's old value of 10000. FieldDEF got DatabaseA's value
>for that key. Also key 9999 got removed from DatabaseB because it did not
>exist in DatabaseA.
>
>How could I achieve this result?
>
>My first thought was to loop through all the rows in DatabaseA, querying
>each key in DatabaseB to see if it exists. If it doesn't, insert a new row
>with DatabaseA's values. If it does exist, update all the fields in
>DatabaseB's row to match DatabaseA's row, except for FieldXYZ. The only
>problem with this is that it doesn't do anything about getting rid of old
>rows in DatabaseB that didn't exist in DatabaseA. I can't do a simple
>delete with an existence test in the Where clause, because we're working
>with two separate databases. I suppose when it's all done, I could loop
>through all of DatabaseB's rows and query for each key in DatabaseA, and if
>it doesn't exist, delete it from DatabaseB. It seems like a lot of steps,
>though.
>
>My second thought was to just delete everything in DatabaseB's table, and
>import it in from DatabaseA. The only problem is how do I preserve the
>values for FieldXYZ in Database B? I was thinking of just storing a linked
>list in the software of DatabaseB's original values of FieldXYZ for each
>key, then updating them when it's all done. But, if something goes wrong
>(program hangs, user aborts, network connection goes down, etc.), Database
>B's original values are lost.
>
>Does anyone have a more elegant solution that I haven't thought of?

Yes - a stored procedure in DatabaseB to perform the update, that accepts
the relevant keys from DatabaseA as input parameters. Loop through
DatasetA, picking up the parameters at each iteration and calling the proc
in B. Of course, put the two connections inside the one transaction and
use tiConcurrency to ensure that you don't get any recursion.

Helen