Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Correct way to set collation in temporary table column TSQL

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?

like image 792
J3FFK Avatar asked May 06 '14 20:05

J3FFK


2 Answers

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.

like image 128
IngoB Avatar answered Oct 18 '22 12:10

IngoB


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.

like image 12
Christian Hayter Avatar answered Oct 18 '22 13:10

Christian Hayter