While the accepted answer (i.e. "use COLLATE DATABASE_DEFAULT") appears to work here, and might even work in many other cases, it is not really addressing the issue and will actually be bad advice for some readers.
The underlying issue of this question is that:
- JOINs are being done on string columns, and
- more than one Collation is being used in those string columns being JOINed
Regardless of the type of table (permanent, local temporary, global temporary, table variable, etc), when mixing multiple strings in an operation (i.e. a predicate, concatenation, UNION, etc) the collation between them must either:
- be the same (for anything that cannot be coerced into another Collation), or
- be coercible into another Collation
Column collations are not coercible, whereas string literals and variables are. This question deals with JOINs between string columns, hence none of them are coercible, hence they all need to be the same collation, or you get the "Cannot resolve the collation conflict" error shown in the question.
Fixing this issue does require using the optional COLLATE clause, but I wouldn't recommend using DATABASE_DEFAULT in this case. The DATABASE_DEFAULT option simply uses the collation of the current database where the query is executing. That collation is not necessarily the collation of any of the columns in these four global temporary tables. The collations for the columns in the four base global temp tables (##A, ##B, ##C, and ##D) are what the collations were in the source tables when these global temp tables were created via SELECT * INTO.... This has nothing to do with the instance-level default collation that tempdb uses since these temp tables were not created via CREATE TABLE ##.... (where the string columns do not specify the COLLATE clause).
In cases like this one where database-level default collations are neither known nor being used, it is best to be intentional / explicit in the collation that you want to use. There are two collations mentioned in the error: Latin1_General_100_BIN and SQL_Latin1_General_CP1_CI_AS. Pick one of those for the COLLATE clause, depending on the intention of this query. Do these JOINs need to be case-insensitive, or do the JOINed columns need to be identical (i.e. a binary comparison)? Assuming you need the values to be identical, do the following:
SELECT *
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key COLLATE Latin1_General_100_BIN
LEFT JOIN ##C as c ON a.key = c.key COLLATE Latin1_General_100_BIN
LEFT JOIN ##D as d on a.key = d.key COLLATE Latin1_General_100_BIN;
OR, depending on how the relationship between these tables works, you can even use different collations to better match different relationships between the different tables:
SELECT *
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key COLLATE Latin1_General_100_BIN
LEFT JOIN ##C as c ON a.key = c.key COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT JOIN ##D as d on a.key = d.key COLLATE Latin1_General_100_BIN;