Walkthrough: Comparing the Data of Two Databases

Article
02/04/2013

This topic applies to:

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

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:

Add Data to the Source Database. In this procedure, you use the Transact-SQL editor to add data to the Customer table in the source database.
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:

SQL Server 2008
Visual Studio Premium or Visual Studio Ultimate

Before you start this walkthrough, you must follow the procedures in Walkthrough: Comparing the Schemas of Two Databases. After you complete this walkthrough, you will have two databases:

The source, CompareProjectDB, has its original structure but contains no data. You customize the project to insert data into the database as part of the post-deployment script.
The target, EmptyDB, is the database that you created in the walkthrough for comparing schema. You then applied only the schema of the AdventureWorks2008 database to EmptyDB. Therefore, EmptyDB contains no data.

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

Add Data to the Source Database

To use the Transact-SQL editor to add data to the source database

On the Data menu, point to Transact-SQL Editor and click New Query Connection.
Specify a connection to the server where the CompareProjectDB is located.

Type the following Transact-SQL statements in the Transact-SQL editor:

use CompareProjectDB;
go
delete from [Sales].[Customer];
go
exec [Sales].[uspNewCustomer] 'Ken J. Sánchez'
exec [Sales].[uspNewCustomer] 'Terri Lee Duffy'
exec [Sales].[uspNewCustomer] 'Roberto Tamburello'
exec [Sales].[uspNewCustomer] 'Rob Walters'
exec [Sales].[uspNewCustomer] 'Gail Erickson'
exec [Sales].[uspNewCustomer] 'Jossef Goldberg'
exec [Sales].[uspNewCustomer] 'Dylan Miller'
exec [Sales].[uspNewCustomer] 'Diane Margheim'
exec [Sales].[uspNewCustomer] 'Gigi Matthew'
go

On the Transact-SQL Editor toolbar, click Execute SQL.

The Transact-SQL statements remove any existing data from the [Sales].[Customer] table, then use the [Sales].[uspNewCustomer] stored procedure to add nine customers. Now that your source database contains data, you can compare it with the target database.

Compare the Data of Two Databases

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. Also, the Data Compare window opens in the background, and Visual Studio automatically assigns it a name such as DataCompare1.
In the Source Database list, click the connection to the CompareProjectDB database..

If the Source Database list is empty, click New Connection. In the Connection Properties dialog box, identify the server on which the CompareProjectDB 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.
In the Target Database list, click Server.EmptyDB.dbo.

If the Target Database list is empty, click New Connection. In the Connection Properties dialog box, identify the server on which the EmptyDB 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 would specify Data Compare Options. For this walkthrough, accept the defaults to display 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 display the columns that they contain. By default, all tables and columns appear 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.
Click Finish to start the comparison.

The comparison starts.

Note

You can stop a data comparison 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 next procedure.

You can now update the data in the target to match the data in the source. For more information, see Updating the Target Database.

To view data-comparison results

Click the [Sales].[Customer] row.

In the Records View pane, the names of the tabs change to reflect the number of records of each type: Different Records (0), Only in Source (9), Only in Target (0), and Identical Records (0).
Click the Only in Source (9) tab.

On the Only in Source (9) tab, each row and each column corresponds to a row or a column in the [Sales].[Customer] 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 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 Transact-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 [Sales].[Customer].
In the details pane, click Only in Source.

The first two rows are for the customers named Ken J. Sánchez and Terri Lee Duffy.

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 Terri Lee Duffy.

On the status bar of the details pane, the number of records to be inserted has been lowered by one record. It no longer matches the number of records that are missing on the target.
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 toolbar, click Export to Editor.

The Transact-SQL editor opens in a connected mode and displays the Transact-SQL script. This window has a name such as DataUpdate_EmptyDB_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.
To synchronize the data of the two databases, run this script by clicking Execute SQL or by pressing F5.

Note

While the script is running, you can cancel the operation by clicking Cancel Query Execution or by pressing Alt + Break.