Hello,

I'm writing a little routine to copy a subset of a production database to my development server for testing. So far I got:

INSERT INTO ...
SELECT ... FROM OPENDATASOURCE(...).database.dbo.table

and this is fine when I need all of the records. However, when I'm pulling just a subset, I need to create some JOINs to limit the results (filtering the child tables to the subset of the parent table). If I:


INSERT INTO ...
SELECT ... FROM OPENDATASOURCE(...).database.dbo.childtable
JOIN OPENDATASOURCE(...).database.dbo.parenttable
ON ...
WHERE [filtering parent table]

Then I assume the filtering of the JOIN is happening on the local server instead of the remote server, which would be bad.

Is there a T-SQL way to submit an entire SELECT statement to another server and have it return the result set? Do I need to create a view on the remote server and connect to that?

Any help, suggestions or alternative ideas would be appreciated.

TIA