Visual Studio SSDT数据比较如何比较单个数据库中的两个表

Trying to do something simple Data Compare in SSDT but proving a bit hard.

尝试在SSDT中做一些简单的数据比较,但证明有点困难。

In one database, I have two tables I want to compare.

在一个数据库中,我有两个我想要比较的表。

These tables have the same schema, just different table names. And I just want to see if this tool will give me a nice way to compare the data in both.

这些表具有相同的模式,只是不同的表名。我只是想看看这个工具是否能给我一个很好的方法来比较两者中的数据。

I.e.

tblOutput
tblOutput_210314

But this picking of two tables to compare against each other in a single database I can't see how to achieve.

但是这个选择两个表在单个数据库中相互比较我看不出如何实现。

Seems like you can only pick a table name which exists in both source and target databases. Since my source and target database is the same, I am basically comparing my table to itself ?

好像您只能选择源数据库和目标数据库中存在的表名。由于我的源数据库和目标数据库是相同的,我基本上将我的表与自己进行比较?

Anyone know of a way to achieve this with Data Compare ?

有人知道通过Data Compare实现这一目标的方法吗?

Visual Studio 2012

Visual Studio 2012

SQL Server Data Tools

SQL Server数据工具

Data Compare

2 个解决方案

#1

Data Compare only supports comparing 2 different databases with matching schemas. Unfortunately you can't do what you are looking to with Data Compare. From the help documentation:

Data Compare仅支持将2个不同的数据库与匹配的模式进行比较。遗憾的是,使用Data Compare无法实现您的目标。从帮助文档:

Requirements

When you compare data in a table or view, the table or view in the source database must share several attributes with a table or view in the target database. Tables and views that do not meet the following criteria are not compared and do not appear on the second page of the New Data Comparison wizard:

比较表或视图中的数据时,源数据库中的表或视图必须与目标数据库中的表或视图共享多个属性。不符合以下条件的表和视图不会进行比较,也不会显示在“新建数据比较”向导的第二页上:

Tables must have matching column names that have compatible data types.

表必须具有匹配的列名,这些列具有兼容的数据类型。

Names of tables, views, and owners are case-sensitive.

表,视图和所有者的名称区分大小写。

Tables must have the same primary key, unique index, or unique constraint.

表必须具有相同的主键,唯一索引或唯一约束。

Views must have the same unique, clustered index.

视图必须具有相同的唯一聚簇索引。

You can compare a table with a view only if they have the same name.

只有在具有相同名称的情况下,才能将表与视图进行比较。

#2

A work around for the tool limitation. If you have two tables with columns in common that need to be compared. One solution requires duplicating the database and comparing views.

解决工具限制的问题。如果您有两个共同列的表需要进行比较。一种解决方案需要复制数据库和比较视图。

For this example we have a database called CoffeeTableBooksDatabase with two tables, table_myBooks and table_aFriendsBooks with common columns authors and titles that need to be compared.

对于此示例,我们有一个名为CoffeeTableBooksDatabase的数据库,其中包含两个表table_myBooks和table_aFriendsBooks,其中包含需要比较的公共列作者和标题。

Duplicate the database with backup then do a restore:

使用备份复制数据库然后执行还原:

Backup the Database (ex: CoffeeTableBooksDatabase)

备份数据库(例如:CoffeeTableBooksDatabase)

Restore the Database with a different name (ex: AnotherCoffeeTableBooksDatabase)

使用其他名称还原数据库(例如:AnotherCoffeeTableBooksDatabase)

Create a common view:

创建一个公共视图:

Create a view (ex: view_SomeBooks) with the same name in both databases

在两个数据库中创建一个具有相同名称的视图(例如:view_SomeBooks)

In the original database (ex: CoffeeTableBooksDatabase), add the first table (ex: table_myBooks) to the view (ex: view_SomeBooks) with all the columns common to both tables (ex: authors and titles)

在原始数据库(例如:CoffeeTableBooksDatabase)中,将第一个表(例如:table_myBooks)添加到视图(例如:view_SomeBooks),其中包含两个表共有的所有列(例如:authors和titles)

In the duplicate database (ex: AnotherCoffeeTableBooksDatabase), add the second table (ex: table_aFriendsBooks) to the view (ex: view_SomeBooks) with all the columns common to both tables (ex: authors and titles)

在重复数据库(例如:AnotherCoffeeTableBooksDatabase)中,将第二个表(例如:table_aFriendsBooks)添加到视图(例如:view_SomeBooks),其中包含两个表共有的所有列(例如:authors和titles)

Now you can compare both databases by just looking at the views comparison. This is not a recommended solution but it works using the tool and its current limitations.

现在,您只需查看视图比较即可比较两个数据库。这不是推荐的解决方案,但它使用该工具及其当前限制。

#1