Compare and Synchronize Data in One or More Tables with Data in a Reference Database
By using Visual Studio Team System Database Edition, you can compare the data in a source database and a target database and specify which values should match. You can then either update the target to synchronize the databases or export the update script to the Transact-SQL (T-SQL) editor or to a file.
For example, you might synchronize databases to update a staging server with a copy of the production data. You might also synchronize one or more tables to populate them with reference data from another database. In addition, you might compare data before and after you run tests as an additional form of verification.
You can compare data in two databases, but you cannot specify a database project (.dbproj) or a .dbschema file for comparison because they do not contain data.
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. For more information about schemas, see Compare and Synchronize Database Schemas.
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.
Each object has a key or an index that determines the other objects to which it corresponds. However, each table or view can have more than one primary key, unique index, or unique constraint. Therefore, you might want to specify which key, index, or constraint to use.
Common Tasks
In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.
Common Tasks |
Supporting Content |
|---|---|
Get hands-on practice: You can follow the introductory walkthrough to become familiar with how to compare the data in two databases. |
|
Set options to control how the data is compared: When you compare data, you can safely ignore identity columns, disable triggers, and disable foreign keys. You can also drop primary keys, indexes, and unique constraints from the update script. |
|
Compare data in tables: After you specify a source and a target database to compare and run the comparison, you can view the results in the Data Compare window. You can view not only details of the differences but also the update script that you can use to synchronize the data. |
How to: Compare the Data of Two Databases Understanding Comparison Results |
Update the target to match the source: After you identify differences between the two databases, you can specify an action for each difference. You can then update the target or export the update script to the T-SQL editor or to a file. You might want to export the script so that you or someone else can review it before you apply the changes. |
|
Troubleshoot problems: You can learn more about how to troubleshoot common problems that you might encounter when you compare and synchronize database schemas. |
Understanding Comparison Results
The following table describes the five columns in the Data Compare window.
Column |
Notes |
|---|---|
Object |
Displays the name of the table or view and a check box that indicates whether the target should be synchronized when you write updates or export the update script. The check box is unavailable for tables or views that do not contain data. |
Different Records |
Displays the number of records in the target that have the same key but not the same data as in the source. Parentheses enclose the number of records that are marked to be updated when you write updates or export the update script. |
Only in Source |
Displays the number of records in the source that do not occur in the target. Parentheses enclose the number of records that are marked to be added when you write updates or export the update script. |
Only in Target |
Displays the number of records in the target that do not occur in the source. Parentheses enclose the number of those records that are marked to be deleted when you write updates or export the update script. |
Identical Records |
Displays the number of records in the target that have the same key and the same data as in the source. These records are not updated when you write updates or export the update script. |
Table and View Details
When you click any table or view in the Data Compare window, the details pane displays all the rows that the table or view contains. Each tab in the details pane displays a different category (Different Records, Only in Source, Only in Target, Identical Records). For each row, you can select or clear the corresponding check box to indicate whether you want to include that change in the update script.
Related Scenarios
- Compare and Synchronize Database Schemas
You can compare the schemas of a database, a database project (.dbproj), or a .dbschema file and then specify which changes you want to include in the update script.