SQL Is it possible to collate twice?

Question

Hello I have created the following script, the problem I am running into is that the two tables I am querying are collated differently, I tried to insert the data into a third table, but because of the collation of the tables I cannot do this, I also cannot collate the tables themselves due to limitations on the database and the program that relies upon it. So is it possible to collate this correctly?

Error:    Msg 468, Level 16, State 9, Line 69
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.





        --Create Table #tmp2(FK_clientids varchar(50))
        --Create table #tmp (phonenums varchar(50))
        --Create table #tmp3 (phonenum varchar(50),fk_applicationid_solref varchar(50),calldata varchar(50),sourcetable varchar(50))
        Delete from #tmp2
        Delete from #tmp
        Use DATABASE2
        INSERT INTO #tmp2
        SELECT fk_clientid
        FROM DM_ClientApplicants
        where FK_ApplicationID in (39591,
        39594,
        39598,
        39596,
        39600,
        39601,
        39603,
        39609,
        39613,
        39585,
        39560)

        Use DATABASE2
        INSERT INTO #tmp
        Select phonenum2 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select PhoneNum1 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select PhoneNum2 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )

        INSERT INTO #tmp
        Select PhoneNum3 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select Partnerphonehome from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select Partnerphonemobile from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select Partnerphonework from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        Use DATABASE1
        insert into #tmp3

Select sourcetable,CallData,PhoneNum,P.FK_ApplicationID from Dial D

        join DATABASE2.dbo.DM_PhoneNumbers P on PhoneNum = PhoneNum1
        join DATABASE2.dbo.DM_Sol s on S.FK_ApplicationID = P.FK_ApplicationID
       Collate Latin_general_CI_AS

        where PhoneNum in
        (
        Select phonenums from #tmp
        )

Answer 1

You can use a collate clause as part of your ON clause. From your posted code, I cant' tell which is which, but it will be something like this:

    join DATABASE2.dbo.DM_PhoneNumbers P 
            on PhoneNum collate SQL_Latin1_General_CP1_CI_AS  = PhoneNum1 

The point being you need to use the collate to modify the collation of one of the join columns to match the other one.