How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?
I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient...


There's nothing inherently inefficient about the Remote Scan operator. It is used, for example, with BULK INSERT, which is extremely efficient. The performance of the Remote Scan depends on what lies behind it, in this case a linked server, various drivers, networking etc.

A Remote Query operator can be more efficient, for example if a predicate or aggregation operation can be pushed to the remote server, rather than retrieving all rows and doing the work locally.


  ...but I'm not sure if it's possible in this case?


There is no choice except to use Remote Scan to read data from a T-SQL remote stored procedure call.