You can create linked server and use OPENQUERY() to send a query to a remote server.

>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