批量插入空列UCS-2小端

Question

我使用bulk insert命令导入一个制表符分隔的文件。我有一个格式文件，如下所示。

8.0
9
1       SQLCHAR       0       50      "\t"                          1     Field1             "SQL_Latin1_General_CP1_CI_AS"
2       SQLCHAR       0       50      "\t"                          2     Field2             "SQL_Latin1_General_CP1_CI_AS"
3       SQLCHAR       0       50      "\t"                          3     Field3             "SQL_Latin1_General_CP1_CI_AS"
4       SQLCHAR       0       50      "\t"                          4     Field4             "SQL_Latin1_General_CP1_CI_AS"
5       SQLCHAR       0       50      "\t"                          5     Field5             "SQL_Latin1_General_CP1_CI_AS"
6       SQLCHAR       0       50      "\t"                          6     Field6             "SQL_Latin1_General_CP1_CI_AS"
7       SQLCHAR       0       50      "\t"                          7     Field7             "SQL_Latin1_General_CP1_CI_AS"
8       SQLCHAR       0       50      "\t"                          8     Field8             "SQL_Latin1_General_CP1_CI_AS"
9       SQLCHAR       0       0       "\r\n"                        9     Field9              "SQL_Latin1_General_CP1_CI_AS"

这对于ANSI文件很好，但是我收到的文件是UCS-2 Little Endian编码。

我尝试使用以下对格式化文件的修改来实现此功能。这会阻止任何错误，但我得到了所有空列的正确行数。如果可能的话，我想避免转换文件。

8.0
9
1       SQLCHAR       0       50      "\t\0"                          1     Field1             "SQL_Latin1_General_CP1_CI_AS"
2       SQLCHAR       0       50      "\t\0"                          2     Field2             "SQL_Latin1_General_CP1_CI_AS"
3       SQLCHAR       0       50      "\t\0"                          3     Field3             "SQL_Latin1_General_CP1_CI_AS"
4       SQLCHAR       0       50      "\t\0"                          4     Field4             "SQL_Latin1_General_CP1_CI_AS"
5       SQLCHAR       0       50      "\t\0"                          5     Field5             "SQL_Latin1_General_CP1_CI_AS"
6       SQLCHAR       0       50      "\t\0"                          6     Field6             "SQL_Latin1_General_CP1_CI_AS"
7       SQLCHAR       0       50      "\t\0"                          7     Field7             "SQL_Latin1_General_CP1_CI_AS"
8       SQLCHAR       0       50      "\t\0"                          8     Field8             "SQL_Latin1_General_CP1_CI_AS"
9       SQLCHAR       0       0       "\r\0\n\0"                      9     Field9               "SQL_Latin1_General_CP1_CI_AS"

Answer 1

就快到了，只需将SQLCHAR更改为SQLNCHAR即可

Answer 2

你的问题帮助我得到了另一个类似问题的答案谢谢

"\t\0“是金的！(愚蠢的UNICODE :( )

(我已经有了SQLNCHAR SQLNVARCHAR部分:)

Answer 3

您的BULK INSERT语句是什么样子的？您可能需要添加DATAFILETYPE

 BULK INSERT {TableName}
 FROM '{FilePath}'
 WITH (DATAFILETYPE = 'widechar', FORMATFILE = '{FormatFilePath}')