Identifying Conflicting Records in Multiple Databases (Mission Impossible)

[wkevindavis]

We have four databases with customer and vendor tables that are not in sync with one another. Users have been free to enter data into these tables at will. They have no PK's and no constraints. (They are poorly designed in my opinion, but that is not in the scope of my assignment, but we will address many of the design issues after we merge all the records.) They have 90 and 70 columns respectiely holding the customer or vendor data.

My assignment is to identify the records between the databases that are conflicting with one another so someone can view the records and identify what should be the correct data.

Part of the problem is that the conflict can occur between any of the columns including customer/vendor ID's or even the customer/vendor names.

I am trying to come up with a way to identify those records that are conflicting without creating cursors and selecting all the columns from each row, and each database into variables to compare as I iterate through the 4 databases.

We are not using the data replication abilities of SQL Server. So using the conflict resolution tool is not a real option.

Does anyone have any ideas that would be easier than creating the monster cursor script I descibed above to identify these records that conflict with one another?

[skhanal]

I am not trying to sell a product (you can get a free trial and never buy it :-D) but Red Gate's data compare product is quite efficient in doing it.

[wkevindavis]

I will check out that product now. Free Trial will work for this assignment. I do not expect to have to do this sort of task again in the future <<crosses fingers>>. I will let you know how it does.

[wkevindavis]

I did check out the Red Hat SQL Data Compare tool. It was almost made to order for what we were trying to do. I think the company is going to wind up buying a few copies of that product.

There was also a couple of other tools my IT Boss thought may come in handy for some migration projects we have coming up before the end of the year. So we may wind up buying more than just the SQL Data Compare.

The only thing that was not a feature of the SQL Data Compare tool was the ability to compare more than two databases concurrently. While this is one of the requirements for our goal, the output would have probably been to cumbersome for anyone in the accounting department to decipher. It does allow you to export results as CSV files and I am using those to import back into Excel spreadsheets that the accountants are accustomed to working with anyway.

Thanks for "selling" me on the Red Hat product. What I had worked on for almost two weeks with no real results will hopefully be completed and formatted into Excel sheets in one day.

[skhanal]

It's Red Gate not Red Hat, that's another product I like :-D

[wkevindavis]

Yes, you are right, it is Red Gate. I just automatically think Red HAT when I see Red Gate. I have had to correct myself a couple of times while discussing the product with my IT manager.

You can call it anything you want, What it is is a very usefull tool for finding conflicting data.