从DataStage 转换到SQL SERVER SP注意点

If one column has one lowercase and one uppercase
DataStage ETL is considering them as separate records but the stored procedure is considering them as one record ...
So it may need to use COLLATE statements.

当转换JOIN和LOOKUP时，尤其要注意在where 条件中加上collate Latin1_General_CS_AI， 例如：

select * from table A t

where t.column_name collate Latin1_General_CS_AI = 'Contribute'

在SP中用临时表时如果遇到以下问题：

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CS_AS” in the equal to operation.

解决思路：

If the creation of temp table is from DDL without specifying any collation, the temp table will use instance default collation; if temp table is created by “select …into..”, the collation definitions are carried over

参考网址：

http://www.sqlnotes.info/2012/05/07/collation-of-temp-tables/

查看Check Column Collation：

select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name