How to join global temp tables in SQL Server? I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this:

SELECT *
INTO ##A


These four tables all have a column with same name 'key' and I want to perform join using this column. I am  using this logic:

SELECT * 
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key
LEFT JOIN ##C as c ON a.key = c.key
LEFT JOIN ##D as d on a.key = d.key


When I execute this code I get the following error:


  Cannot resolve the collation conflict between "Latin1_General_100_BIN"
  and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


I have been stuck here and cannot seem to resolve it. 

Any help/advice/suggestions much appreciated.
SELECT * 
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key collate database_default
LEFT JOIN ##C as c ON a.key = c.key collate database_default
LEFT JOIN ##D as d on a.key = d.key collate database_default


Should eliminate the problem

But the underlying problem is that your (source) tables have different collations. This should be avoided.

This is a good source for some more information:
Questions about sql server collations