SSMS 2014 - DB Collation for unicode/ multiple languages

Question

In SSMS 2014, I have a DB with collation set as Chinese_Simplified_Stroke_Order_100_CI_AI, in which I create a table for a regular process with about 50 columns in it. It basically contains sales data of some products. Few of these columns have integers as values and others contain English text. Two of these columns, however contain values in the form of text which is in Chinese. A sample script that I update the column with is as below:

ALTER TABLE table_xyz ALTER COLUMN comments NVarchar (max) COLLATE Chinese_Simplified_Stroke_Order_100_CI_AI

UPDATE table_xyz SET comments = CONCAT('以下的邮件是专为您的最终客户所准备。', Account_Name)

This has been working fine till now. I have a similar table in a second DB with collation set as Japanese_CI_AS_KS_WS and accordingly the comments in this table are in Japanese. Sample update statement as below:

ALTER TABLE table_abc ALTER COLUMN comments NVarchar (max) COLLATE Japanese_CI_AS_KS_WS

UPDATE table_xyz SET comments = CONCAT('次の電子メールは顧客のためのテンプレートです', Account_Name)

Now, I have been tasked to transfer these tables to an existing DB that has SQL_Latin1_General_CP1_CI_AS collation set as default. The problem is I whenever I update the above two tables in this new DB, all I get in the output is '???????'

I have tried searching for solutions and have observed the below:

1. Many suggestions include converting the datatype to unicode.
2. A few people at my workplace suggested changing the collation of the column.
3. Use UTF-8 as default character set

As per my knowledge the first two are already taken care of when I run the Alter table statement. The third point seems valid for MySQL and not SQL Server.

Also, if I import the table from the respective DBs directly along with the data, the column values are displayed correctly (in Chinese and Japanese text). However, when I truncate and try to load the data I face the problem. I would be unable to import data in this way, since the end objective is to keep all tables in a single DB and purge the remaining DBs.

Hope I've made the problem statement clear.

Answer 1

You need to use N at the beginning of string literal containing UNICODE characters.

The code in your first example should be like this.

CONCAT(N'以下的邮件是专为您的最终客户所准备。', Account_Name)