Does SQL Server transform text in any way when performing a BULK INSERT?

Question

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:

Cannot insert duplicate key row in object 'dbo.tbl_Name' with unique index '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?

Answer 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.

Answer 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?