Bob, In determining what is new, why deal with all the data in the ODBC table. Create an RBase table only with the key value (OrderNumber). Create a proper index on the 1 column with a proper such as DELETE ROWS FROM KeyValueTable WHERE OrderNumber in (SELECT OrderNumber IN RBaseDetailTable)
This will leave you just the new OrderNumber in the ODBC table. You can then do INSERT ... from ODBC table(s) to RBASE table(s) Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 --- On Tue, 12/21/10, A.G. IJntema <[email protected]> wrote: From: A.G. IJntema <[email protected]> Subject: [RBASE-L] - RE: Archiving logic To: "RBASE-L Mailing List" <[email protected]> Date: Tuesday, December 21, 2010, 9:00 AM 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 databaseis not Rbase and I cannot modify any data/records in the two tables. The two tables are sales headerand sales detail. The issue is that these tables have records added on a daily basis and are purged on a regular schedulefor 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
