Resolving 'collation conflict' error joining with temp table

1. Create a table with collation "SQL_Latin1_General_CP1_CI_AS"

drop table tbl1;
create table tbl1 (name varchar(100) collate SQL_Latin1_General_CP1_CI_AS);

2. Populate the table

insert into tbl1 select name from sys.databases;

select * from tbl1;

3. Verify Table collation and database collation

DECLARE @cDbName VARCHAR(50) = (SELECT DB_NAME()) ;
DECLARE @cDbCollation VARCHAR(50) = CAST(DATABASEPROPERTYEX(@cDbName, 'Collation') AS VARCHAR(50)) ;

SELECT   [SchemaName]       = S.[name]
        ,[TableName]        = T.[name]
        ,[ColumnName]       = C.[name]
        ,[ColumnCollation]  = C.collation_name
        ,[DatabaseCollation]= @cDbCollation
FROM    
        sys.schemas S
        JOIN sys.tables T ON ( T.schema_id = S.schema_id )
        JOIN sys.columns C ON ( C.object_id = T.object_id )
WHERE   
        t.name = 'tbl1'
ORDER BY 1,2
;

Output

4. Join the table with temporary table created with "Hebrew_CI_AS" (collation inheritted for tempdb)

select a.name
into #tbl1
from tbl1 a
join sys.databases b ON a.name = b.name
;

Output

Msg 468, Level 16, State 9, Line 27
Cannot resolve the collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

5. To avoid the error above, add "collate database_default" to the join

select a.name
into #tbl1
from tbl1 a
join sys.databases b ON a.name = b.name collate database_default
;

6. Drop tbl1

drop table #tbl1;