none
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. RRS feed

  • 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.
    Wednesday, December 18, 2013 5:54 PM

Answers

  • 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 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 an SQL Agent Job, and schedule it to run for ever 2 Hrs.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, December 19, 2013 6:32 AM

All replies

  • It seems no one really understands your question nor your requirements.  I suggest you consider searching on the topics:  linked servers, ETL, replication.  IME it is unusual for rows in a single table to require synchronization between 2 databases - especially on different servers.  Perhaps you should reconsider the design or requirements that depend on this approach since it seems, to me at least, rather fragile and complicated.   The sql server agent can be used to perform tasks on a schedule.
    Wednesday, December 18, 2013 7:53 PM
  • We have multiple testers that reports its results to a local sql database table that resides on each of the testers.  I need these records to be copied or merged onto the main SQL server where the users can do their queries for reports and such.  what is the best way to get the records from the testers local database to the main database?
    Thursday, December 19, 2013 2:01 AM
  • 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 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 an SQL Agent Job, and schedule it to run for ever 2 Hrs.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, December 19, 2013 6:32 AM
  • How big is the table on server B? Is that possible to bring the all data into a server A and merge the data locally?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 19, 2013 6:40 AM
    Answerer