Scheduled job to copy data

Question

I need help with setting up a scheduled job.

I have two SQL Server databases on two different servers. The job would do SELECT on database A and INSERT on database B. When something changes in database A, the job would compare what had changed and did an update on database B.

Is this possible if I have SQL Server 2008 R2 Management Studio?

Thank you very much in advance.

Answer 1

I would suggest to make a Replication if possible. Read more about it here.

Otherwise if you really need a own job you have two ways.

1. Execute the Job with your SQL Agent every X minutes/hours. Check your new data and execute the INSERT-statement.
2. You can create a trigger on the source table which sets a flag in a table or on the sourcetable itself after an insert is executed. Your job on the target server executes every x minutes or even seconds and check the source table. After that he can evaluate if a changed happen and just copy the flagged rows to your target.

Answer 2

You could set up one-way replication between the two servers and let it take care of everything for you.

Or, you could add server B as a linked server then take responsibility for the record inspection and crafting the insert/update/delete statements yourself.

Have you tried either?