Many to one table live data synchronization

SQLDBA 21 Reputation points
2022-04-28T15:01:58.887+00:00

Hello,

I have multiple tables in different databases on a SQL server 2019, and I have a join query that pulls rows from those multiple tables and insert into one table, This insert query is scheduled to run every 30 minutes, in every run it truncates the target table and loads all the rows. Instead of truncate and load all the rows every time is there a way to set up live data synchronization between those multiple tables and one target table based on the the join query results?

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,666 questions
0 comments
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2022-04-28T17:28:49.02+00:00

    Without knowing exactly what you are doing, it is hard to guess.

    A simpler solution is likely to change your process from truncate/load to use MERGE instead.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-04-29T05:24:35.567+00:00

    Hi SQLDBA-2876,

    In addition, you can try to use the Merge Join Transformation in the SSIS to help you Synchronize the Data.
    Please refer to the articles which might be helpful:
    Merge Join Transformation
    Synchronize Table Data Using a Merge Join in SSIS

    Best Regards,
    Amelia

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments