I need an efficient way to compare 4 large tables.
Posted on 2004-09-07
There really has to be a better way to do this. Any help is greatly appreciated.
Currently we receive two very large text files from our client every week. This is an automated procedure on our client's side and unfortunately they are not willing at all to modify this process and include a unique identifier on either table.
Currently we import these two tables, then drop the previous tables (assuming the import went properly), then index the new tables. We then perform this at our 6 other offices around the wold.
I want to modify this so we import the new tables and then compare them to the old and only send the changes (either an add or a delete) to the clients.
One table contains approx. 11 million records and the other has approx. 24 million records.
There are no unique identifiers on either table.
I have tried 3rd party tools such as Red Gate SQL Data Compare and it requires a unique indentifier (and if I had this I could do it myself).
I have also tried the undocumented getchecksum() however when I compare the old and new tables with getchecksum() it shows about 99% of the records have a difference and this should only be around 10 - 15%.
Any thoughts on this are greatly appreciated!