高级SQL数据通过多个表进行比较

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.

考虑下面的情况。两个表(A和B),在两个环境(DEV&TEST)中,在这些表中有记录。如果您查看表的内容,您就会明白功能数据是相同的。我的意思是除了PK和FK值之外,Roger这个名字与水果和蔬菜有关。

In DEV environment :

Table A
1 Roger
2 Kevin

表A 1罗杰2凯文

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

表B(斜体字段是表A中的FK)1 1水果2 1蔬菜3 2肉

In TEST environment :

Table A
4 Roger
5 Kevin

表A 4罗杰5凯文

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

表B(斜体字段是表A中的FK)7 4水果8 4蔬菜9 5肉

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)

我正在寻找一个SQL数据比较工具,它会告诉我上面的情况没有区别。或者,如果有,它将生成具有正确顺序的插入和更新脚本(先插入A然后插入B)

8 个解决方案

#1

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

我在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.

我联系了他们,他们告诉我这个功能将在下一版本的Data Compare中实现。

#2

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.

您也可以尝试使用Devart的dbForge Data Compare。它允许您比较数据并设置所需操作的结果。看看它的性能和简洁性。

#3

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

我使用SQL Compare作为Red Gate Software的SQL Toolbelt的一部分

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

我对它的性能和功能非常满意。

#4

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.

我有一个存储过程来比较任何两个表。事实证明,这也适用于视图。所以我可以比较两个视图,每个视图返回一个“扁平”版本的模式。我可以省略像身份/代理键这样的列,或者指示我的proc忽略它们。

As far as inserting, I'm not sure.

就插入而言,我不确定。

#5

Try SQL Data Examiner. I like this tool.

试试SQL Data Examiner。我喜欢这个工具。

#6

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?

大多数数据比较工具允许您忽略列;但是,您希望它们不忽略FK列,但确定Test.TableA的4等于Dev.TableA的1,以便表B中的第一行相同。它是否正确?

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.

我不知道有任何工具可以做到这一点。现在针对您的第二个问题,大多数数据比较工具将以正确的顺序为您生成脚本。您可以查看Redgate的数据比较。

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

如果您有Visual Studio Developer Edition,它附带了一个数据比较工具(可单独安装,称为Visual Studio Database Edition)

#7

If you are using sql server 2008 you can use a merge statement and it will insert update or delete based on what you want to do.

如果您使用的是sql server 2008,则可以使用合并语句,它将根据您要执行的操作插入更新或删除。

#8

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:

使用SqlDiffFramework找到任何差异很简单,SqlDiffFramework是一个用于数据源的可视差异程序(而不是像典型的diff程序那样的文件)。顾名思义,它是一个框架:您必须为它提供两个要比较的查询,一个用于您的DEV环境,另一个用于您的TEST环境。在您的特定情况下,查询将是相同的,如:

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.)

SqlDiffFramework在一个窗口中提供了两个并排查询工具。您只需将一侧指向DEV数据库,将另一侧指向TEST数据库,然后运行两个查询以获取并比较数据。 (您甚至可以保存快照,然后在您选择的快照上运行差异,以查看随着时间的推移会出现的差异(如果有的话)。)

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

但请注意,SqlDiffFramework不会解决请求的第二部分,而是生成脚本以使它们匹配。

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

[SqlDiffFramework,版本1.0,介绍2010.04.29,是开源的,免费提供。]

#1