The best way to synchronize (one way) a table in SQL Server
Tags: sql, database
Published: 2020-09-30 09:31:23

To synchronize two tables, you can use either built-in tools like [SSIS] (https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-ver15) or third-party ones.
You can learn about synchronizing table data one-way via [SSIS](https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-ver15) by referring to this link:

![Fig.1 Table data one-way synchronization package](https://ondicesu.sirv.com/Images/fig1.png "Fig.1 Table data one-way synchronization package")

You can also synchronize two tables with the help of a third-party tool called [dbForge Data Compare](https://www.devart.com/en/dbforge/sql/datacompare/) that integrates with [SSMS](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15):

![Fig.2 Comparing table data in SSMS](https://ondicesu.sirv.com/Images/fig2.png "Fig.2 Comparing table data in SSMS")

Before the comparison process starts, you can configure the source and target servers and databases:

![Fig.3 Setting up the source and the target](https://ondicesu.sirv.com/Images/fig3.png "Fig.3 Setting up the source and the target")

Column mapping can be set for the table:

![Fig.4 Column mapping](https://ondicesu.sirv.com/Images/fig4.png "Fig.4 Column mapping")

“Columns details” displays column definition information from two tables: source database on the left and target database on the right.
Column mapping is particularly useful when column names are different in the source and the target.

As a result, we can see data differences:

![Fig.5 Data differences](https://ondicesu.sirv.com/Images/fig5.png "Fig.5 Data differences")

Column visibility and sorting can also be configured:

![Fig.6 Column visibility and sorting settings](https://ondicesu.sirv.com/Images/fig6.png "Fig.6 Column visibility and sorting settings")

You can generate a script or perform data synchronization  after finding the differences.
Keep in mind that it’s important to make sure table definitions match before comparing or synchronizing data between two tables. This can be done with the help of standard methods, like [Visual Studio](https://visualstudio.microsoft.com/) or [SSDT](https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15), or via 3rd-party tools.

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

![Fig.7 Creating a new SQL Server Database project](https://ondicesu.sirv.com/Images/fig7.jpg "Fig.7 Creating a new SQL Server Database project")

Next, you’ll need to import the database:

![Fig.8 Opening the database import window](https://ondicesu.sirv.com/Images/fig8.jpg "Fig.8 Opening the database import window")

In the window that will be opened, you need to configure a connection to the necessary database and then press *Start* to initiate the import process:

![Fig.9 Configuring and  starting the database import process](https://ondicesu.sirv.com/Images/fig9.jpg "Fig.9 Configuring and  starting the database import process")

After this, you will be able to see folders, subfolders, and definitions of database objects in the project:

![Fig.10 Project after import](https://ondicesu.sirv.com/Images/fig10.jpg "Fig.10 Project after import")

In the same way, you will need to create a project and perform import for the second database.

Now, to compare the database schemas, you just need to right-click any of the two projects and then click *Schema Compare*… in the context menu:

![Fig.11 Navigating to the database schema comparison window](https://ondicesu.sirv.com/Images/fig11.jpg "Fig.11 Navigating to the database schema comparison window")

As a result, a database schema comparison window will be opened.
Here you need to select source and target projects and press *Compare* to start the comparison process:

![Fig.12 Database schema comparison window](https://ondicesu.sirv.com/Images/fig12.jpg "Fig.12 Database schema comparison window")

Also, you can use other tools for database comparison, such as dbForge [Schema Compare](https://www.devart.com/en/dbforge/sql/schemacompare/), which integrates with [SSMS](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15):

![Fig.13 Comparing database schemas in SSMS](https://ondicesu.sirv.com/Images/fig13.png "Fig.13 Comparing database schemas in SSMS")

You can configure the source and target for schema comparison:

![Fig.14 Configuring the source and target for schema comparison
](https://ondicesu.sirv.com/Images/fig14.png "Fig.14 Configuring the source and target for schema comparison")

You can also set table mapping in the corresponding tab:

![Fig.15 Selecting a table for mapping](https://ondicesu.sirv.com/Images/fig15.png "Fig.15 Selecting a table for mapping")

![Fig.16 Configuring field mapping in the table](https://ondicesu.sirv.com/Images/fig16.png "Fig.16 Configuring field mapping in the table")

“Columns details” displays column definition information from two tables: source database on the left and target database on the right.

This is particularly useful when column names are different in the source and the target.

After schemas are compared, you can select the necessary table and see the table definition differences in two databases:

![Fig.17 Table schema comparison between two databases](https://ondicesu.sirv.com/Images/fig17.png "Fig.17 Table schema comparison between two databases")

At the bottom left, the definition code of the source database table is displayed, and that of the target database is displayed at the bottom right.

If needed, a synchronization script for any schema table can be generated.