SQL with Manoj

–> Question:

How do I merge data from Table1 on Server-1 to final table on Server-2 with a Stored Procedure to execute every so many hours?
 

–> My Answer:

A basic approach would be to create a Linked Server in Server2 that will point to Server1.

On Server2 you can create a Stored Procedure, which will read the table via Linked Server from Server1. You can use MERGE statement to merge (or INSET/UPDATE/DELETE) records in final table on Server2 form table1 in Server1.

If you are dealing with millions of records then you can go with Incrememtal/Delta load approach, here you will have to store the timestamp of previous load in some meta-data table.

To keep executing every 2 Hrs, you will need to create a SQL Agent Job, and schedule it to run for ever 2 Hrs.

You can also use SSIS or other ETL tool as a better approach to setup this load.
 

Ref Link.