OpenQuery into a local table

Question

I have a very simple script. The purpose is to pull data from a linked server into a table in another database

DECLARE @SQLString VARCHAR(2000)

SET @SQLString = 'SELECT * from OPENQUERY(MyLinkedServer, 
    ''SELECT 
    ...
    ...
    FROM SomeSchema.SomeTable'')'

INSERT INTO MySchema.MyTable
            (
              ............
            )
            EXEC(@SQLString)

In what fashion is this executed? If I just set up the @SQLString and EXEC, results will be displayed to my screen. Does this mean that running the script above will bring the data from the linked server to my local machine, then off to my sql server database, or does the data go straight from my linked server to sql server?

score 1 · Accepted Answer · answered May 24 '13 at 19:14

The data always goes from the linked server to the SQL Server before going to the client (if applicable), as shown in the documentation for both distributed queries and linked servers. Since your client is only connected to SQL Server, not directly to the linked server, this is the only way it could work.

OpenQuery into a local table

1 Answers1