Updating linked database c
Conventional wisdom states that the linked server approach will go out to the link, pull the data to the local server, and then apply any logic (filters, joins, etc.) on the local server.Open queries are useful when you are not referencing multiple servers in one query.It’s generally fast as SQL does not break it into multiple operations and does not perform any local action on the output received.Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.
Excellent reading references : How are you measuring efficiency? Which one will consume fewer resources on the target? How many rows and what kind of data types are the columns in these rows?
Are you sure you can execute a TVF through a linked server (is the target SQL 2008 or later? How are you ensuring a 1:1 migration of this data if you're pulling from a TVF? Update 1 It sounds like you're looking for ETL (Extract-Transform-Load).
I'd recommend SSIS (SQL Server Integration Services) with which you can pull the data from the source, apply the transformations that you need, and then load those into your target.
This sounds like it'd be a pretty straightforward package (depending on the transformations).
This is called PULLING Data as you are executing the query on destination server and pulling data from source server.
This will be a lot faster and less resource intensive compared to the former one (depending on how much data is being pulled).