Creating a T-SQL temp table on another server machine

Question

I'm using SQL Query Analyzer to build a report from the database on one machine (A), and I'd like to create a temp table on a database server on another machine(B) and load it with the data from machine A.

To be more specific, I have a report that runs on machine A (machine.a.com), pulling from schema tst. Using SQL Query Analyzer, I log into the server at machine.a.com and then have access to the tst schema:

USE tst;
SELECT *
FROM prospect;

I would like to create a temp table from this query window, only I'd like it built on another machine (call it machine.b.com). What syntax would I use for this? My guess is something like:

CREATE TABLE machine.b.com.#temp_prospect_list(name varchar(45) Not Null, id decimal(10) Not Null);

And then I'd like to load this new table with something like:

INSERT INTO machine.b.com.#temp_prospect_list VALUES (
    USE tst;
    SELECT *
    FROM prospect; );

Thanks for the help.

Answer 1

The syntax to access a remote server in T-SQL is to fully qualify any table name with the following (brackets included when necessary):

[LinkedServer].[RemoteDatabase].[User].[Table]

So, for example, to run a SELECT statement on one server that accesses a table on another server:

SELECT * FROM [machine.b.com].tst.dbo.table7;