title: Joining 2 #temp tables with data from different servers SQL Server
body: <p>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.</p>
  
  <p>First I did</p>
  
  <pre><code>SELECT ID 
        ,A
        ,B
        ,C
  INTO ##Temp1 
  FROM [DB1].[dbo].[Table1]
  WHERE ID IS NOT NULL
  </code></pre>
  
  <p>Then in a different window I did</p>
  
  <pre><code>SELECT A_ID
      ,Q
      ,R
  INTO ##Temp2  
  FROM [DB2].[dbo].[Table2] 
  </code></pre>
  
  <p>But in my attemp to join the two </p>
  
  <pre><code>SELECT A_ID 
      ,Q 
      ,R 
      ,A
      ,B
      ,C   
  FROM ##Temp2 AS TableA
  LEFT JOIN ##Temp1 as TableB
  ON TableA.A_ID = TableB.ID
  </code></pre>
  
  <p>I got the error </p>
  
  <pre><code>Invalid object name '##Temp1'.
  </code></pre>
  
  <p>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.  :/ </p>