I want to compare 2 table each on their own server. I want to avoid constantly querying between the 2 server. Would something like a temp table work well in this case? Do I need to use linked server and extract the table into a temp table so I only query the server once? Thanks.
Note you should include which SQL Server version you're using, as this will impact what answers are applicable to your use case.
These can be used to synchronize the data from a remote server to a local server, so your querying can all be contained to one server.