How to Compare Data in Tables of SQL Server Databases

This article explains how to perform data comparison in SQL Server databases using dbForge Data Compare for SQL Server and dbForge Studio for SQL Server. The detailed description of steps and explanatory screenshots will give you an idea of how these tools cope with the task.

How it works

dbForge Data Compare for SQL Server finds data differences in Source and Target SQL Server database tables. Source is a database, the content of which is inserted into Target after synchronization. Target is the database that is updated (synchronized) with the results of schema comparison to make it the same as Source.

When comparison is completed, you are able to view detailed information on data differences in Data Comparison window. You can filter the results, analyze them and further perform synchronization to make the Source and Target tables identical.

Stages of data comparison

  • Creating connections to SQL Server servers where the needed databases are located.
  • Selecting Source and Target and setting up additional parameters before comparing table data.
  • Mapping required databases in Source and Target manually if automatic mapping cannot satisfy your specific needs.
  • Analyzing comparison results and selecting actions to apply during synchronization (e.g., Insert, Delete, Update objects).
  • Obtaining synchronization script for further usage or synchronizing data in databases.

Getting data differences of two SQL Server databases

Now let's compare table data of two SQL Server databases using Data Comparison Wizard of dbForge Data Compare for SQL Server.

Click New Data Comparison on the Start Page to open New Data Comparison Wizard. Select the type of database (Database or Backup) for Source and Target.

Choose Database Type for Source and Target

Select the required connection from the Connection list of Source and Target.

Choose Required Connection

You can create new connection or edit the selected one by clicking New and Edit correspondingly.

When connections are selected, databases are automatically mapped and displayed in the Database fields. But you can select the needed Source and Target databases from the Database lists manually. Click Next.

Note, if you want to compare tables in the same database, you may just select Source connection and database and then click the Copy source settings to target button.

Copy Source Settings to Target

Adjust comparison options. Click Next.

Choose Options to Compare SQL Server Databases

Select objects that you want to compare on the Mapping wizard page. If necessary, specify key columns and the list of columns for comparison.

Map Columns

Click Compare to start comparison.

The wizard closes, and the data comparison document opens, where you can view data differences in database tables.

View Differences Between SQL Server Databases

Analyzing data differences of two SQL Server databases

Data comparison document gives you a clear view of differences found in the databases and offers rich settings to analyze them.

Filter comparison results in the grid using the Filter list to narrow down the results.

Filter Comparison Results in the Grid

See differences between compared objects in the text comparer under the grid.

Analyze Differences

If required, include only necessary objects into synchronization by selecting check boxes next the needed objects. To exclude objects from synchronization, clear the corresponding check boxes.

Read on to find out how to synchronize data in SQL Server databases.

See also How to Compare Rows and Columns in SQL Server Databases