How do I compare 2 tables for subtle differences?

[buddyrich2]

I have 2 large tables with the same field names, structure, etc. One table may have some subtle differences in some of the data from the other one. What would be the best (quickest) way to find the records in the second table that are different from the first table?

Thanks!

 

[brigmar]

First off, you'd need a unique index so that you can compare like records, otherwise you have no idea what records in table A to compare to table B.

Assuming you have that index :

scan table B for records that don't exist in table A

SET RELATION TO <keyfield> INTO A IN B
SCAN FOR EOF('A')
** This is a record in B that isn't in A
ENDSCAN

scan table A for records that don't exist in table B
For records with the same key, SCATTER to objects and compare for differences.

SET RELATION TO <keyfield> INTO B IN A
SCAN
IF EOF('B')
** This is a record in A that isn't in B
ELSE
** This record exists in B, Now check for equality
SCATTER MEMO NAME oRecordA
SELECT B
SCATTER MEMO NAME oRecordB
IF !COMPOBJ(oRecordA, oRecordB)
** This record is different between A & B
ENDIF
ENDIF
ENDSCAN


Note: The COMPOBJ returns .F. if the fields are defined differently between the two tables.

 

[JRB-Bldr]

You could always write a small program which would establish a relationship between the two tables and flag where the field contents were not the same (possibly saving field names + values out to some Results table) .

If your program built indexes on each field under examination and then sequentially utilized the relationship on each field you could end up with a list of differences.

Good Luck,
JRB-Bldr

 

[fitedgar]

If you are only concerned with changes in certain fields you can use :


SELECT * from table_A where field1 + field2 + field3 NOT IN (SELECT field1 + field2 + field3 FROM table_B ) ;
INTO CURSOR ac_cursor

You can expand field1 + field2 + field3 + field4 etc.

EMC

 

[buddyrich2]

I ended up taking brigmar's advice and it worked fine. Warning to those who use it in the future: make sure the table structure is EXACTLY the same. One difference (i.e. column width) will cause all the records to show as different. Thank you brigmar.

Thanks to you too fitedgar. I actually could have just done what you said if I had known about it.