Correct way to set collation in temporary table column TSQL

Question

I have a temporary table which gets data inserted using bulk insert. However, when I want to update data from temp table to a normal table it gives collation problems. I know how to solve this by using something like:

UPDATE RegularTable
SET r.Column1 = t.ColumnA
FROM RegularTable r INNER JOIN #TEMP t ON
r.Column1 COLLATE DATABASE_DEFAULT = 
t.ColumnA COLLATE DATABASE_DEFAULT

But, is there a way to set the collation in the temporary table immediately so you don't have to use collate in the join? Something like:

CREATE TABLE #TEMP
Column1 varchar(255) COLLATE database_default,
Column2 varchar(60) 

Is this correct coding and do you have to set the collation once per table or per column? And if the collation is set in the table, can you exclude the collate from the join then?

Solution 1

You can use COLLATE database_default in the temp table definition using the syntax you describe, and that will make each column collation-compatible with your database.

You have to set it explicitly per column. There is no table-level default collation. There is a database-level default collation, but for tempdb this is always equal to the default collation of the model database, which by default is the server collation.

If you set the collation on the table column, you can still override it in a query, as you have already experienced.

Solution 2

We ran into the same problem right now. Instead of adding the collation to each temp table join, we just changed the temp table creation to a table variable declaration.