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 have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. 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, but I'm not sure if it's possible in this case? (The remote stored procedure returns about 1.5 million rows.)

This is my query on local server A:

CREATE TABLE #FinalResults (Field1 INT, Field2 VARCHAR(100))
DECLARE @DynamicSQL NVARCHAR(MAX) = ''

SELECT @DynamicSQL = @DynamicSQL + 
'
SELECT Field1, Field2
FROM OPENQUERY(MyRemoteServerB, ''EXEC sp_MyRemoteStoredProcedure'')
'

INSERT INTO #FinalResults
EXEC sp_ExecuteSQL @DynamicSQL;


This is my query in my remote procedure sp_MyRemoteStoredProcedure on my remote server B:

CREATE TABLE #Results (Field1 INT, Field2 VARCHAR(100))

INSERT INTO #Results
SELECT Field1, Field2
FROM Database1.dbo.Table1
WHERE DateField &gt;= 'Date1'
    AND DateField &lt; 'Date2'

UNION

SELECT Field1, Field2
FROM Database2.dbo.Table1
WHERE DateField &gt;= 'Date1'
    AND DateField &lt; 'Date2'

SELECT Field1, Field2
FROM #Results


Relevant execution plan operators:
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.