Walkthrough: Comparing the Data of Two Databases

  • Article

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 these 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 this walkthrough, you must have access to the following products:

  • Microsoft SQL Server 2000 or Microsoft SQL Server 2005

  • Visual Studio Team System Database Edition

Before you start this walkthrough, follow the steps in the procedures in Walkthrough: Comparing the Schemas of Two Databases. After you complete this walkthrough, 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.

You must have permissions to read data from the source and target databases.

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

  1. On the Data menu, point to Data Compare, and then click New Data Comparison.

    The New Data Comparison wizard appears. Also, the Data Compare window opens in the background, and Visual Studio automatically assigns it a name such as DataCompare1.

  2. 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. In 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.

  3. 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. In 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.

  4. If you wanted to restrict which records appear in the comparison results, you would specify Data Compare Options. For this walkthrough, accept the defaults to see all records.

  5. 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 and will be compared. The key that is used to compare the records appears for each table or view in the list. Tables and views must meet certain criteria to be compared. For more information, see Compare and Synchronize Data in One or More Tables with Data in a Reference Database.

    Note

    If you do not want to customize which tables and views are compared, you can click Finish instead of Next.

  6. To exclude a particular table or column, clear its check box. To include a particular table or column, select its check box.

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

  1. 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).

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

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

  1. In the Data Compare window, click [dbo].[Products].

  2. In the details pane, click Only in Source.

    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.

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

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

  1. In the Data Compare toolbar, 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 want to save the update script, click Save.

  2. 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 by pressing Alt + Break.

See Also

Tasks

How to: Compare Database Schemas

How to: Compare the Data of Two Databases

Concepts

Compare and Synchronize Database Schemas

Terminology Overview of Database Edition