Is there an entry date in the table? ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, December 21, 2010 7:34 AM 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
