title: Using COLLATE DATABASE_DEFAULT when querying the UNION when the table headers are unknown?
body: <p>I am currently using code of the form</p>
  
  <pre><code>SELECT * FROM ##Temp1
  UNION
  SELECT * FROM ##Temp2
  </code></pre>
  
  <p>which (because the temporary tables are populated from tables in 2 different databases, which each use a different collation) results in the error message</p>
  
  <pre><code>Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.
  </code></pre>
  
  <p>Until now, when faced with an error such as this, I have been using the COLLATE DATABASE_DEFAULT method, which has been possible because the headings contained within the tables have been constant.</p>
  
  <p>However, I am now faced with a collation conflict between 2 tables where the table headings are not constant. How might I resolve this issue?</p>
  
  <p>One possible solution that I can think of is to loop through the table headers for <code>##Temp1</code> in the <code>tempdb.dbo</code> table and generate a string dynamically which specifies the column headings used in the temporary tables. However, this seems unneccessarily clumsy.</p>