Dear Team,
I have a requirement to download/pull the data into my tables from another database server tables/views.
I have to pull whenever there is a change in the data (Update/Insert/Delete) in another server tables automatically.
Which method is best way to do this.
Thank you in advance.
Options:
Linked Server
OpenQuery
Use Trigger to find if their is any Insert/Update/Delete
SSIS
Service Broker
Can we also consider transnational Replication for this.
Hi guys check this useful link .I hope it will help you
https://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/
Since the requirement is a near instantaneous and fully automatic synchronization of multiple tables with a chance of "catching up" if the connection is temporarily broken, satya's suggestion is probably the best suggestion for this task.
The real question is, however, is there really a requirement for "near instantaneous"? Transactional replication has it's own set of headaches and can interfere with the truncation of logs during backups if there is a break or other problem with the connection between the two boxes for any reason.
If "near instantaneous" is not negotiable, then, yes, use transactional replication even with some of the pain it causes. If something like "once per hour" is good enough, then do something across a linked server or with OPENROWSET as previously suggested.
Service Broker could work and without the problems associated with the log file if the connection is broken but it has it's own set of headaches both for setup and when the connection breaks.
IMHO, there's no way in hell that I'd use SSIS for this. 
Thank you JeffModen for detail explanation.
Thank you all for the quick replies.