I'm trying to use a BULK INSERT statement to populate a large (17-million-row) table in SQL Server from a text file. One column, of type nchar(17) has a UNIQUE constraint on it. I've checked (using some Python code) that the file contains no duplicates, but when I execute the query I get this error message from SQL Server:
我正在尝试使用BULK INSERT语句从文本文件填充SQL Server中的大型(1700万行)表。一个类型为nchar(17)的列对其具有UNIQUE约束。我检查(使用一些Python代码)该文件不包含重复项,但是当我执行查询时,我从SQL Server收到此错误消息:
Cannot insert duplicate key row in object 'dbo.tbl_Name' with unique index 'IX_tbl_Name'.
无法在对象'dbo.tbl_Name'中插入具有唯一索引'IX_tbl_Name'的重复键行。
Could Server be transforming the text in some way as it executes BULK INSERT? Do SQL Server databases forbid any punctuation marks in nchar columns, or require that any be escaped? Is there any way I can find out which row is causing the trouble? Should I switch to some other method for inserting the data?
服务器可以在执行BULK INSERT时以某种方式转换文本吗? SQL Server数据库是否禁止nchar列中的任何标点符号,或者要求任何标点符号转义?有什么方法可以找出造成麻烦的行吗?我应该切换到其他一些插入数据的方法吗?
2 个解决方案
#1
Your collation settings on the column could be causing data to be seen as duplicate, whereas your code may see it as unique. Things like accents and capitals can cause issues under certain collation settings.
列上的排序规则设置可能导致数据被视为重复,而您的代码可能会将其视为唯一。像口音和大写字母这样的东西可能会在某些整理设置下导致问题。
#2
Another thought too would be that empty or null values count as duplicates, so your Python code may not have found any text duplicates, but what about the empties?
另一个想法是空值或空值被视为重复,因此您的Python代码可能没有找到任何文本重复,但是空的呢?
#1
Your collation settings on the column could be causing data to be seen as duplicate, whereas your code may see it as unique. Things like accents and capitals can cause issues under certain collation settings.
列上的排序规则设置可能导致数据被视为重复,而您的代码可能会将其视为唯一。像口音和大写字母这样的东西可能会在某些整理设置下导致问题。
#2
Another thought too would be that empty or null values count as duplicates, so your Python code may not have found any text duplicates, but what about the empties?
另一个想法是空值或空值被视为重复,因此您的Python代码可能没有找到任何文本重复,但是空的呢?