Visual Studio SSDT Data Compare how to compare two tables in a single database
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
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.