Joining 2 #temp tables with data from different servers SQL Server

Question

I need to join data from some tables that are located on different servers. I do not have admin rights on the servers so I can't create linked servers which is the immediate response every time someone asks about this. I thought I would try creating a couple of temp tables and then joining those, but while I was able to create the #temp tables successfully and used some WHERE clauses to reduce the amount of data in the tables which is a bonus, I can't figure out how to join the two. I am using SQL Server Management Studio and it seems like a given SQL query is unable to "see" both temporary tables at once. Are the temporary tables still stored on the servers that the data is collected from? I was under the impression that they were all stored in some temp database that I could access all at once.

First I did

SELECT ID 
      ,A
      ,B
      ,C
INTO ##Temp1 
FROM [DB1].[dbo].[Table1]
WHERE ID IS NOT NULL

Then in a different window I did

SELECT A_ID
    ,Q
    ,R
INTO ##Temp2  
FROM [DB2].[dbo].[Table2] 

But in my attemp to join the two

SELECT A_ID 
    ,Q 
    ,R 
    ,A
    ,B
    ,C   
FROM ##Temp2 AS TableA
LEFT JOIN ##Temp1 as TableB
ON TableA.A_ID = TableB.ID

I got the error

Invalid object name '##Temp1'.

This is my first time attempting something like this and I feel like I'm missing something quite simple here but I was unable to find anything in all my searches. :/

Answer 1

at least five options are here, and all of them are not available for a 'normal' user:

1. Create a linked server (you don't need sysadmin permission, you need just 'ALTER ANY LINKED SERVER' permission). But this server should be created just once, so your db admyn can do it for you.

or

2. Export the data to a flat file and import on target server (it can be automated)

or

3. Create an empty DB, copy data, make a backup, restore a backup on target server - can be automated as well (here you need certain permissions to create a db, make backups/restore, but sysadmin permissions are not necessary)

or

4. Replication

or

5. SSIS

...

If you can create two identical tables on two servers, you can use CMS (Central Management Server) to run the query simultaneously on two servers. Merged results may be inserted to some table and then processed.