Advanced SQL Data Compare throught multiple tables

Question

Hello,

Consider the situation below.
Two tables (A & B), in two environments (DEV & TEST), with records in those tables.
If you look the content of the tables, you understand that functionnal data are identical.
I mean except the PK and FK values, the name Roger is sill connected to Fruit & Vegetable.

In DEV environment :

Table A
1 Roger
2 Kevin

Table B (italic field is FK to table A)
1 1 Fruit
2 1 Vegetable
3 2 Meat

In TEST environment :

Table A
4 Roger
5 Kevin

Table B (italic field is FK to table A)
7 4 Fruit
8 4 Vegetable
9 5 Meat

I'm looking for a SQL Data Compare tool which will tell me there is no difference in the above case.
Or if there is, it will generate insert & update scripts with the right order (insert first in A then B)

Thanks a lot guys,

Grégoire

Answer 1

I use SQL Compare as part of the SQL Toolbelt from Red Gate Software

I'm VERY happy with it's performance and functionality.

Answer 2

Most data compare tools allow you to ignore columns; however, you want them to not ignore the FK columns but determine that Test.TableA's 4 is equal to Dev.TableA's 1 so that the first row in Table B is the same. Is this correct?

I don't know of any tool that can do this. Now for your second question, most of the data compare tools will generate scripts in the correct order for you. You can look into Redgate's Data Compare.

If you have Visual Studio Developer Edition it comes with a Data Compare tool (Available as separate install called Visual Studio Database Edition)

Answer 3

I have a stored proc which compares any two tables. It turns out that this also works on views. So I can compare two views which each return a "flattened" version of a schema. I can either leave out columns like identity/surrogate keys or instruct my proc to ignore them.

As far as inserting, I'm not sure.

Answer 4

I found this on Red Gate : http://www.red-gate.com/messageboard/viewtopic.php?t=8401#top

I contacted them and they told me this feature will be implemented in the next version of Data Compare.

Answer 5

Also you can try dbForge Data Compare from Devart. It allows you to compare data and set that consequence of actions that you need. Check out it's perfomance and simplicity.

Answer 6

Zidsoft CompareData http://www.zidsoft.com/

You can set up comparison key columns to whatever columns you want (in this case it would be the logical keys for your tables) and you can ignore columns on per table-basis. Also you can generate sql sync script using your comparison key columns rather than the surrogate keys.

In this case you may also want to use a query comparison rather than table comparison. CompareData will let you compare the resultsets of two queries. Your query will join on the surrogate foreign key and you can include the surrogate foreign key in the resultsets and ignore it from the comparison or not include it in the resultsets to begin with. One draw back to using query comparison in CompareData is that it does not currently support synchronizing or generating sync script for a query comparison. If generating a sync script is a requirement then you can create views for the your queries (comparing views data works like comparing/synchronizing table data).

Answer 7

Finding any discrepancies is straightforward with SqlDiffFramework, a visual diff program for data sources (rather than files like your typical diff program). As the name implies, it is a framework: you have to feed it two queries to compare, one for your DEV environment and one for your TEST environment. In your particular case, the queries will be identical, something like:

SELECT A.person, B.foodgroup
FROM A JOIN B on A.IdPerson = B.IdPerson
ORDER BY A.person, B.foodgroup

SqlDiffFramework provides two side-by-side query tools in one window. You simply point one side to your DEV database and the other to your TEST database, then run both queries to fetch and compare the data. (You can even save snapshots and then run a diff on the snapshots of your choice to see how differences, if any, emerge over time.)

Note, however, that SqlDiffFramework does not address the second part of your request, to generate scripts to make them match.

[SqlDiffFramework, version 1.0, introduced 2010.04.29, is open-source and freely available.]

Answer 8

Try SQL Data Examiner. I like this tool.