Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to synchronize data from a table between different databases using Trigger

Reading Time: 4 minutes

Speak guys!
In this post today I'm going to bring a very simple solution, but it solved a problem of a person who asked me for help to synchronize data from a table between different bases, in the same instance, in the simplest way possible, where you insert, update or erases data from the source table and all changes are replicated to a mirror table in another base.

As the focus of the solution is to be fast, simple, work in any version and edition of SQL Server, not require a Job to keep updating, having the data updated almost in real time and there are only a few tables instead of the entire base, I thought the best solution for this scenario is to use Triggers.

Are there any other solutions to solve this problem of replicating data between different databases? Certainly!

  • Change Data Capture: It's fast, it's simple to use, but it doesn't work on any edition of SQL Server and stores all changes in a history base (no need in this case).
  • Temporal Tables: It's fast, it's simple, but it only works from SQL Server 2016 onwards, it will require a job to sync the data
  • Always On Availability Group: It won't be easy to implement and it won't be fast. It won't replicate just a few tables, but the entire base and it doesn't work on all versions of SQL Server
  • Replication: It won't be easy to implement or fast, but it meets the requirements to work on all versions and editions of SQL Server, it doesn't require a job, the data is updated almost in real time and you can replicate just a few tables

Creating the source and mirror tables

Trigger that will mirror the original table

With the trigger below, I will replicate all INSERT, UPDATE and DELETE operations done on the original table to the mirror table.

Remember that if you change the structure of the source table, such as a new column, remove/rename existing columns, for example, you must always apply the same changes to the mirrored table. Otherwise, this will generate errors in trigger execution, unless you change the trigger code to specify exactly the column names in the INSERT and include the changes in the UPDATE as well.

testing the solution

Let's now test if the trigger is really working as expected.

I'm going to insert 10 random rows into the dbo.Sales table in the Base1 database:

Result:

Now I'll do an UPDATE on the original table:

Result:

And finally, finish the tests with a DELETE on the original table:

Result:

HEADS UP: This trigger will be executed every INSERT/UPDATE/DELETE performed on the original table. This means that if you perform a bulk DML operation, such as inserting a block of rows or updating the entire table, for example, each affected row will trigger a trigger execution, which can make the process itself very slower. Be careful when using DML trigger because of this.

And that's it folks!
I hope you liked this super simple tip, but it can help a lot of people in everyday life with a simple solution, but it works 🙂