问题:
不同的电脑上的两个数据库DB1、DB2
DB1数据库字符集: SQL_Latin1_General_CP1_CI_AS
DB2数据库字符集: Chinese_Taiwan_Stroke_CI_AS
从Excel中导入数据到临时表中,然后和数据库表中现有数据做对比,存在就更新,不存在就插入
*merge dbTable
using (SELECT CICID,CurrencyType,SUM(Amount)as Amount FROM #SCTemp
group by CICID,CurrencyType) as SCTemp
on SCInfo.CurrencyType=SCTemp.CurrencyType
when matched
then update set SCInfo.TotalAmount =SCInfo.TotalAmount+ SCTemp.Amount
when not matched
then insert values(SCTemp.CICID,1,SCTemp.CurrencyType,SCTemp.Amount,0,0,0,’Black Friday Plan - MVP Refunds’);*
然后on后面的条件判断就出错了
错误信息:
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Chinese_Taiwan_Stroke_CI_AS” in the equal to operation.
百思不得其解,又想用下面语句修改数据库字符集,
alter database HOHCS_Lagacy collate Chinese_Taiwan_Stroke_CI_AS
结果失败。
解决方案:
后来网上查询不同数据库字符集之间如何查询,原来是这样,需要关键字“Collate Database_Default”转换字符集,把语句改成如下:
on SCInfo.CurrencyType=SCTemp.CurrencyType Collate Database_Default
结果查询成功!
总结:不同字符集之间比较,关键字“Collate Database_Default ”起作用
另外:
查看SQLSERVER的字符集设置sql如下
use master
go
select SERVERPROPERTY(N’edition’) as Edition –数据版本,如企业版、开发版等
,SERVERPROPERTY(N’collation’) as Collation –数据库字符集
,SERVERPROPERTY(N’servername’) as ServerName –服务名
,@@VERSION as Version –数据库版本号
,@@LANGUAGE AS Language –数据库使用的语言,如us_english等