How to Synchronize a Table in SQL Server

September 23rd, 2020

Database synchronization is an everyday necessity. You need it regularly when you have several similar databases that serve the same “user.”

The typical situation is, they must be identic, but you can’t grant it. For instance, the databases are administered by different teams that can update them independently. Or, there are identic databases that perform differently. So, you have to define why they differ.

The problem is, whenever you have to compare two tables for differences, the process is too complicated. Even if your database specialists develop a working method for one synchronization, any new requirement will cause a significant change. You’ll have to start the work from scratch. As a result, the SQL server table synchronization turns into an overcomplicated and time-consuming job. If this task is one-time, it becomes even worse, as it does not justify the effort.

Still, the SQL server sync task remains needed, and the need brings the supply. Instead of preparing long and complicated instructions, use the automated tool. First, it can be some built-in tool like SSIS.

One-way synchronization package via SSIS

Fig.1 Table data one-way synchronization package

The second option is to use third-party tools, like dbForge Data Compare for SQL Server.

dbForge Data Compare used for comparing table data

Fig.2 Comparing table data in SSMS

In this article, we review the SQL server table synchronization process where you apply automated tools.

The Standard Way of the Database Comparison in SQL Server

The comparison starts with the source and target servers and databases. You configure them:

Setting up the source and the target for database comparison in SQL Server

Fig.3 Setting up the source and the target

Set the column mapping for the table:

Setting the column mapping for the table

Fig.4 Column mapping

The “Columns details” section displays the referring information from two tables: the source database is on the left, and the target database is on the right.

Column mapping is especially useful id the column names are different in the source and the target. This way, we see data differences:

The differences in data between the source and target

Fig.5 Data differences

Also, you configure the column visibility and sorting:

Configuring visible columns and sorting  in SQL Server

Fig.6 Column visibility and sorting settings

When you find all the differences, you can generate a script or perform the data synchronization.

Important! You need to make sure that the table definitions match before you compare or synchronize the data between two tables. You can do it with the standard methods, like Visual Studio or SSDT, or via 3rd-party tools.

To do this, create a new SQL Server Database project as follows:

Creating a new SQL Server database project

Fig.7 Creating a new SQL Server Database project

Next, import the database:

Calling the database import command

Fig.8 Opening the database import window

A new window will open. In that window, you need to configure the connection to the necessary database.

After that, press Start to initiate the import process:

Configuring and starting the database import process

Fig.9 Configuring and starting the database import process.

In the result of the actions described above, you will see folders, subfolders, and the definitions of database objects in the project:

The project with folders, subfolders, and definitions after import

Fig.10 Project after import

To create a project and import the data for the second database, you should act in the same way. 

To compare the database schemas, you only need two clicks:

  1. Right-click any of the two projects;
  2. Click “Schema Compare…” in the context menu.
Navigating to the database schema comparison window

Fig.11 Navigating to the database schema comparison window

It opens the database schema comparison window. There, you select the source and the target projects, and press Compare to start the comparison process:

Initiating the schema comparison process in SQL Server

Fig.12 Database schema comparison window

Using dbforge Schema Compare to Explore the SQL Difference between Two Tables

dbForge Schema Compare integrates with SSMS. This SQL compare tool is a dedicated solution that helps you to make the SQL database comparison and analyze the SQL data differences. Also, it allows you to synchronize SQL Server data easily with the help of its embedded wizard.

Comparing database schemas with dbForge Schema Compare that integrates into SSMS

Fig.13 Comparing database schemas in SSMS

The process of the data comparison is the same as described above.

How to find difference between two tables in SQL Server?

  1. First of all, you configure the source and the target for schema comparison:

    Configuring the source and target for schema comparison

  2. Set the table mapping in the corresponding tab, if needed:

    Selecting a table for mapping

  3. Configure the field mapping in the table and select the columns for comparison

    Like we have already noticed, the “Columns details” section shows the definition of information from two tables’ columns. The source database is on the left, and the target database is on the right. It matches the case when the columns’ names are different in the source and the target.
    Configuring field mapping in the table

  4. Viewing the definition differences in the databases

    After schemas are compared, you can select the necessary table and view the table definition differences in two databases:
    Table schema comparison between two databases

Note the code at the bottom:

  • On the bottom left, you get the definition code of the source database table;
  • On the bottom right, you see the definition code of the target database.

Then you can generate the synchronization script for any schema table.

dbForge Schema Compare is a powerful and functional tool. You can use it to compare the databases and sync them. You can also automate the SQL server update tasks with the help of this solution. It is an easy and convenient way for any user to do the usually draining and over-complicated job.

Evgeniy Gribkov

Comments are closed.