question

SQLDBA-2876 avatar image
0 Votes"
SQLDBA-2876 asked AmeliaGu-msft answered

Many to one table live data synchronization

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered SQLDBA-2876 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your quick answer, I have 100 million rows in my one of source table, any performance issues with MERGE?

0 Votes 0 ·

No. I have done 100 billions of rows with merge on a daily basis.

0 Votes 0 ·
SQLDBA-2876 avatar image SQLDBA-2876 TomPhillips-1744 ·

Thank you for your response. I will try it and let you know.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.