在临时表列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?

这是正确的编码，您是否必须为每个表或每列设置一次排序规则？如果在表格中设置了排序规则，那么您可以从连接中排除整理吗？

2 个解决方案

#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.

您可以使用您描述的语法在临时表定义中使用COLLATE database_default，这将使每个列与您的数据库兼容。

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.

您必须按列显式设置它。没有表级默认排序规则。存在数据库级默认排序规则，但对于tempdb，它始终等于model数据库的默认排序规则，默认排序规则是服务器排序规则。

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

如果在表列上设置排序规则，您仍然可以在查询中覆盖它，就像您已经体验过的那样。

#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.

我们现在遇到了同样的问题。我们只是将临时表创建更改为表变量声明，而不是将排序规则添加到每个临时表连接。

#1