Visual Studio SSDT Data Compare how to compare two tables in a single database

Question

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

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 ?

• Visual Studio 2012
• SQL Server Data Tools
• Data Compare

Answer 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:

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.

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

Duplicate the database with backup then do a restore:

1. Backup the Database (ex: CoffeeTableBooksDatabase)
2. Restore the Database with a different name (ex: AnotherCoffeeTableBooksDatabase)

Create a common view:

1. Create a view (ex: view_SomeBooks) with the same name in both databases
2. 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)
3. 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)

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.