SQL Server 2005 - 批量插入问题
我从 sql server 2005 中的批量加载中收到以下错误消息，并且在想出可以尝试解决此问题的想法后。
第 2 行第 4 列（类型）出现批量加载数据转换错误（类型不匹配或指定代码页的字符无效）。
BULK INSERT dbo.BobTable FROM 'C:\DataFiles\Bob.txt'
WITH (FIELDTERMINATOR = '","', FIRSTROW=2,ROWTERMINATOR = '\n',DATAFILETYPE='char')
有什么想法吗？
"id","altid","altid2","type"
123456789.00,"ABC1234","ABC1234","R"
我无法更改源文件，但可以更改它正在插入的表。 （别问我为什么，小数点已经被插入到 ID 的末尾了……我只需要处理它。）
CREATE TABLE [dbo].[BOB](
[id] [nvarchar](50) NULL,
[Altid] [nvarchar](50) NULL,
[Altid2] [nvarchar](50) NULL,
[type] [nvarchar](50) NULL
)
I am getting the following error message from a bulk load in sql server 2005, and was after ideas I can try to solve this problem.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (type).
BULK INSERT dbo.BobTable FROM 'C:\DataFiles\Bob.txt'
WITH (FIELDTERMINATOR = '","', FIRSTROW=2,ROWTERMINATOR = '\n',DATAFILETYPE='char')
Any ideas?
"id","altid","altid2","type"
123456789.00,"ABC1234","ABC1234","R"
I cannot change the source file, but I can change the table that it is being inserted into.
(Don't ask me why, but the decimals have been inserted at the end of an ID...I just have to deal with it.)
CREATE TABLE [dbo].[BOB](
[id] [nvarchar](50) NULL,
[Altid] [nvarchar](50) NULL,
[Altid2] [nvarchar](50) NULL,
[type] [nvarchar](50) NULL
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果没有格式文件的帮助，批量插入不擅长处理引号和逗号分隔符。您需要创建或生成一个，然后使用批量插入引用它。
在您的示例中，第一个字段以
,"结尾，而不是","，因此 SQL 认为该字段太长。这是一个很好的起点：
创建格式文件
这一篇是关于格式文件的应用，其中还有您的问题的示例：使用格式文件批量导入数据
我以前使用过这些方法，如果您的文件格式随着时间的推移而变化，维护可能会很困难，但它为您提供了
BULK INSERT过程所需的灵活性。另外，您可以使用 SSIS 来完成这些工作吗？ SSIS 可以比 BCP 格式文件更轻松地处理引号和逗号分隔的文件。如果您有选择，我完全推荐它。
Bulk Insert isn't good at handling Quote and Comma delimiters without help from a format file. You would need to create or generate one and then reference that with your Bulk Insert.
In your example, the first field ends with a
,", not","so SQL thinks the field is too long.Here's a good starting point:
Creating a Format File
And this one on the application of the format file, which also has an example of your issue: Using a Format File to Bulk Import Data
I've used these methods before and it can be challenging maintain if your file format changes over time, but it gives you the necessary flexibility with the
BULK INSERTprocess.Also, are you able to use SSIS for any of this work? SSIS can handle the quote and comma delimited files with MUCH less pain than a BCP Format file. If you have the option, I fully recommend it.