BULK INSERT SQL SERVER 的乐趣 - 为指定的代码页键入不匹配或无效字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7490542/
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
Fun with BULK INSERT SQL SERVER - type mismatch or invalid character for the specified codepage
提问by l--''''''---------''''''''''''
i am inserting data that looks like this:
我正在插入如下所示的数据：
AA00000111 PSNH-OT J, SMITH 03/01/2011 10/11/1957 42 Male Hartford NH Lorazepam Benzodiazepines C
AA00000151 PSNH-OT BEN, HARRY 03/06/2011 07/18/1969 42 Male Hartford NH Fentanyl Synthetic Opioids C
AA00000151 PSNH-OT URA, HARRISON 03/06/2011 07/18/1969 29 Male Hartford NH Norfentanyl Synthetic Opioids C
AA00000181 PSNH-OT WAYNE, GRIFFON 03/06/2011 09/01/1982 75 Female Hartford NH cTHC (Marijuana metabolite) Illicits C
with this bulk insert:
使用此批量插入：
use RadarDataMining
go
BULK INSERT tblRadarsAC
FROM 'C:\PerfLogs\radars.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
and i am getting these errors:
我收到这些错误：
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 4 (date_tested).
Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
the fourth field type is date
第四个字段类型是 date
it does not like my date field for some reason. i do not understand why. what am i doing wrong?
由于某种原因，它不喜欢我的日期字段。我不理解为什么。我究竟做错了什么？
回答by HLGEM
Create a staging table to play with where the date field is specified as a varchar and not a date and see if you can bulk insert into that. It is possible that you have some sort of nonprintable character in the field and thus it isn't a date to the bulk insert.
创建一个临时表来使用，其中日期字段被指定为 varchar 而不是日期，看看是否可以批量插入。您可能在该字段中有某种不可打印的字符，因此它不是批量插入的日期。
Once it is a table. do a select like this
一旦它是一张桌子。做这样的选择
Select 'XX'+field4+'XX'
选择'XX'+field4+'XX'
If you see a space bewteen the the Xs and the date, you have an unprintable character. If you want to know what the character is you can use the ASCII () function.
如果您在 X 和日期之间看到一个空格，则您有一个不可打印的字符。如果你想知道字符是什么，你可以使用 ASCII() 函数。
