Hi Bob.
Have a look at this solution:
First load the new data into a (empty) temp table which is a copy of the
original and be sure to put an index on the unique key in the temp table.
Example: project temp <tablename> from <orginal_tableName> using
all where limit = 0
Create index on ….
Then update existing date in the archive table by using the unique key
Then append new rows from the temp table using the ‘not in’ clause.
The process should not take more than a few seconds.
Succes
Tony
From: [email protected] [mailto:[email protected]] On Behalf Of
[email protected]
Sent: dinsdag 21 december 2010 14:34
To: RBASE-L Mailing List
Subject: [RBASE-L] - Archiving logic
I have an ODBC database that has two tables I need to archive data from. This
foreign database
is not Rbase and I cannot modify any data/records in the two tables. The two
tables are sales header
and sales detail.
The issue is that these tables have records added on a daily basis and are
purged on a regular schedule
for older records. (Usually anything closed older than 3 months) I need to
create a running archive of all sales records and I need to update this table
on a daily basis.
I could do:
Append "ODBC Table" to "Rbase Table" where OrderNumber not in (Sel OrderNumber
from "RBase Table")
(or similar logic)
but this is not efficient nor probably even possible. The Rbase table will
have well over 200,000 rows and will grow while the ODBC table will have
100,000+ rows. It would probably take hours to run the above if it would run
at all. I cannot flag the ODBC records in any way as I cannot change the data
structure or data in the ODBC database.
What makes this difficult is that the unique index on the foreign data base is
order number, but it is not numeric, it is alphanumeric. So an order could be
OR123456 and the next order could be AB123456.
(I did not create this database and cannot change this logic) If this was a
numeric field, I could find the maxium order number in the Rbase table and
append any records from the ODBC tables with a larger order number. However
this will not work with an alphanumeric field.
Any thoughts of an efficient method to obtain only new records?
Thanks,
-Bob
