在 SQL Server 中将 varchar 转换为 nvarchar 失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22710427/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting varchar to nvarchar in SQL Server failed
提问by daveb
I have SQL Server table that contains columns of type varchar(50)as a result of a CSV import using the SQL Server Import wizard.
我有 SQL Server 表，其中包含varchar(50)使用 SQL Server 导入向导进行 CSV 导入的结果列类型。
I was wanting to know how I can change this data type to nvarchar(9)without getting a SQL Server truncation error.
我想知道如何更改此数据类型nvarchar(9)而不会出现 SQL Server 截断错误。
I tried doing a bulk update to set the data types and column sizes that I need but still had the truncation error message when I tried to load the csv into the empty database table I created (with my required data types that I need).
我尝试进行批量更新以设置我需要的数据类型和列大小，但是当我尝试将 csv 加载到我创建的空数据库表中时仍然出现截断错误消息（使用我需要的数据类型）。
Grateful for any help.
感谢任何帮助。
回答by M.Ali
Since you are willing to lose data and nvarchar will only be able to store 9 non-unicode charaters, then select only 9 characters from your source table, You do the truncation rather than Sql server doing it for you.
由于您愿意丢失数据并且 nvarchar 只能存储 9 个非 unicode 字符，因此仅从源表中选择 9 个字符，您执行截断操作而不是 Sql 服务器为您执行此操作。
The Following Query will trim any White spaces from the strings, Then take only 9 characters from the string and convert them to NVARCHAR(9) for you.....
以下查询将修剪字符串中的任何空格，然后仅从字符串中取出 9 个字符并将它们转换为 NVARCHAR(9) 为您.....
CREATE TABLE New_TABLE (Col1 NVARCHAR(9), Col2 NVARCHAR(9))
GO
INSERT INTO New_TABLE (Col1, Col2)
SELECT CONVERT(NVARCHAR(9),LEFT(LTRIM(Col1), 9))
,CONVERT(NVARCHAR(9),LEFT(LTRIM(Col2), 9))
FROM Existing_Table
GO
回答by Habeeb
Bulk insert into temp table with varchar(50) and insert to actual table
使用 varchar(50) 批量插入临时表并插入实际表
insert into tableName
select cast(tempcolumn as nvarchar(9)) from temptable
回答by HoGo
And it is also important to check field types of destination table. Just spent 3 hours because of same error with random casting, trimming, substring and at the end noticed, that colleague created table with too short field lengths. I hope it helps somebody...
检查目标表的字段类型也很重要。由于随机转换、修剪、子字符串的相同错误，只花了 3 个小时，最后注意到，该同事创建的表的字段长度太短。我希望它可以帮助某人...
回答by Niek
If you encounter this error during Import/Export Tasks, you can use the select cast(xxx as nvarchar(yyy)) as someNamein the "Write a query to specify the data to transfer" option
如果在导入/导出任务期间遇到此错误，可以使用select cast(xxx as nvarchar(yyy)) as someName“编写查询以指定要传输的数据”选项中的
回答by DavidCC
varchar and nvarchar only use the length needed for the data stored. If you need unicode support certainly convert to nvarchar, but modifying it from 50 to 9 - what is the point?
varchar 和 nvarchar 仅使用存储数据所需的长度。如果您需要 unicode 支持肯定会转换为 nvarchar，但将其从 50 修改为 9 - 有什么意义？
If your data is ALWAYS exactly 9, consider using char(9), and following one of the transformation suggestions above...
如果您的数据始终恰好为 9，请考虑使用 char(9)，并遵循上述转换建议之一...
