Walkthrough: Comparing the Data of Two Databases
In this walkthrough, you compare the data of two databases, generate a Data Manipulation Language (DML) script from the differences, and then use that script to populate the target with data from the source.
You will follow the procedures in this walkthrough:
Compare the data of two databases. In this procedure, you find differences in data, display them in a grid, and generate a DML script that expresses them.
Update the target database. In this procedure, you see two ways in which you can update the target so that it matches the source.
Prerequisites
Before you can complete that walkthrough, you must have the following products installed:
Microsoft SQL Server 2000 or Microsoft SQL Server 2005
Microsoft Visual Studio Team Edition for Database Professionals
Before you start this walkthrough, follow the steps in the procedures in Walkthrough: Comparing the Schemas of Two Databases. After you complete that walkthroughWalkthrough: Comparing the Schemas of Two Databases, you will have two databases:
The source, Northwind, has its original structure and contains its original data.
The target, EmptyNW, is the database that you created in the walkthrough for comparing schema. You then applied only the schema of the Northwind database to EmptyNW. Therefore EmptyNW contains no data.
Compare the Data of Two Databases
This section contains two procedures. In the first procedure, you compare databases and produce results. In the second procedure, you examine those results.
To compare the data of two databases
On the Data menu, point to Data Compare, and then click New Data Comparison.
The New Data Comparison wizard appears. Use this to configure the data comparison. To configure the comparison, you must specify the server on which each database resides, the type of authentication to use when you connect to it, and the name of each database that you want to compare.
Also, the Data Compare window opens in the background, and Visual Studio automatically assigns it a name such as DataCompare1.
In the New Data Comparison wizard, select the source database, Northwind. In the Source Database list, its name appears in the form Server.Northwind.dbo.
If the Source Database list is empty, click New Connection. On the Connection Properties dialog box, identify the server on which the Northwind database resides and the type of authentication to use when you connect to the database. Then, click OK to close the Connection Properties dialog box and return to the New Data Comparison wizard.
Select the target database, EmptyNW. In the Target Database list, its name appears in the form Server.EmptyNW.dbo.
If the Target Database list is empty, click New Connection. On the Connection Properties dialog box, identify the server on which the EmptyNW database resides and the type of authentication to use when you connect to the database. Then, click OK to close the Connection Properties dialog box and return to the New Data Comparison wizard.
If you wanted to restrict which records appear in the comparison results, you can specify Advanced Data Compare Options. For this walkthrough, accept the defaults to see all records.
Click Next.
On the second page of the New Data Comparison wizard, you can refine a selection of tables and views to compare. All the tables of the database are listed under the Tables node. You can expand individual tables to see the columns that they contain. By default, all tables and columns are selected. This means that they will all be compared.
Note
If you do want to customize which tables and views are compared, you can click Finish instead of Next.
To exclude a particular table or column, clear its check box. To include a particular table or column, select its check box.
Click Finish to start the comparison.
The comparison starts.
Note
You can stop a data comparison operation that is in progress by clicking Stop on the Data Compare toolbar.
When the comparison is finished, the data differences between the two databases appear in a table in the Data Compare window. For more information about how to view these results, see the following procedure.
You can now choose to update the data in the target to match that in the source. For more information, see Updating the Target Database.
To view data-comparison results
Click the [dbo].[Orders] row.
The names of the tabs in the Records View pane change to include the number of records of each type: Different Records (0), Only in Source (830), Only in Target (0), and Identical Records (0).
Click the Only in Source (830) tab.
On the Only in Source (830) tab, each row and each column corresponds to a row or a column in the [dbo].[Orders] table.
Note
You can use these tabs to control the data that is propagated to the target. For more information, see Write Updates to Target Database.
Update the Target
You can update the data in the target either directly from the Data Compare window or by using the Transact SQL (T-SQL) editor. This section describes both options.
After you compare the data, the differences appear in the grid in the Data Compare window. For each table or view in the Object column, a check box indicates whether that object would be included in an update operation. The columns show which changes of which types were indicated by the data comparison. In this case, because the target is empty except for default objects, only the Only in Source column shows changes that would be made.
Write Updates By Using Data Compare Options
To update the data in the target using the update actions that appear in the Data Compare window, use the following procedure:
- Update the Target Database
Write Updates By Using the T-SQL Editor
To export, examine, and change an update script and then use it to change the target, use the following procedure:
- Examine and Run the Synchronization Script
Update the Target Database
To update the target
In the Data Compare window, click [dbo].[Products].
In the details pane, click Only in Target.
The first two rows are named Chai and Chang.
On the status bar of the details pane, the status bar states the number of records that are missing on the source and the number of records that will be deleted on the target. These two numbers match.
Clear the check box in the row for Chai.
On the status bar of the details pane, the number of records to be deleted has been lowered by one record. It no longer matches the number of records that are missing on the source.
In the Data Compare window, click Write Updates.
The update actions that were listed in the Data Compare window are implemented. This synchronization changes the target to match the source.
Note
While the target is being updated, you can cancel the operation by clicking Stop Writing to Target.
Examine and Run the Synchronization Script
To examine the synchronization script
In the Data Compare window, click Export to Editor.
The T-SQL editor opens in a connected mode and displays the T-SQL script. This window has a name such as Server.Northwind - DataUpdate_EmptyNW_1.sql. Because you have write access in this window, you can change the script. If you make changes, click Save when you finish.
To synchronize the data of the two databases, run this script by clicking Execute SQL or pressing F5.
Note
While the script is running, you can cancel the operation by clicking Cancel Query Execution, or pressing Alt + Break.
See Also
Tasks
How to: Compare the Schemas of Two Databases
How to: Compare the Data of Two Databases
Concepts
Terminology Overview of Team Edition for Database Professionals