带FIRSTROW参数的SQL批量插入跳过以下行 [英] SQL Bulk Insert with FIRSTROW parameter skips the following line

问题描述

我似乎无法弄清楚这是怎么发生的。

I can't seem to figure out how this is happening.

以下是我尝试批量插入SQL Server 2005的文件示例：

Here's an example of the file that I'm attempting to bulk insert into SQL server 2005:

***A NICE HEADER HERE***
0000001234|SSNV|00013893-03JUN09
0000005678|ABCD|00013893-03JUN09
0000009112|0000|00013893-03JUN09
0000009112|0000|00013893-03JUN09

这是我的批量插入语句：

Here's my bulk insert statement:

BULK INSERT sometable
FROM 'E:\filefromabove.txt
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR= '|',
ROWTERMINATOR = '\n'
)

但是，由于某种原因，我只能得到的输出是：

But, for some reason the only output I can get is:

0000005678|ABCD|00013893-03JUN09
0000009112|0000|00013893-03JUN09
0000009112|0000|00013893-03JUN09

第一条记录总是被跳过，除非我完全删除了标题并且不使用FIRSTROW参数。怎么可能？

The first record always gets skipped, unless I remove the header altogether and don't use the FIRSTROW parameter. How is this possible?

谢谢！

推荐答案

I不要认为您可以使用批量插入 / BCP 跳过其他格式的行。

I don't think you can skip rows in a different format with BULK INSERT/BCP.

运行此命令时：

TRUNCATE TABLE so1029384

BULK INSERT so1029384
FROM 'C:\Data\test\so1029384.txt'
WITH
(
--FIRSTROW = 2,
FIELDTERMINATOR= '|',
ROWTERMINATOR = '\n'
)

SELECT * FROM so1029384

我得到：

col1                                               col2                                               col3
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
***A NICE HEADER HERE***
0000001234               SSNV                                               00013893-03JUN09
0000005678                                         ABCD                                               00013893-03JUN09
0000009112                                         0000                                               00013893-03JUN09
0000009112                                         0000                                               00013893-03JUN09

即使在标头数据中，它似乎也需要'|'，因为它会将其读入第一列-在第一列中包含换行符。显然，如果您包含一个字段终止符参数，它期望每一行必须都有。

It looks like it requires the '|' even in the header data, because it reads up to that into the first column - swallowing up a newline into the first column. Obviously if you include a field terminator parameter, it expects that every row MUST have one.

您可以使用预处理来删除该行步。另一种可能性是仅选择完整的行，然后对其进行处理（排除标题）。或使用可以处理此问题的工具，例如SSIS。

You could strip the row with a pre-processing step. Another possibility is to select only complete rows, then process them (exluding the header). Or use a tool which can handle this, like SSIS.

这篇关于带FIRSTROW参数的SQL批量插入跳过以下行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！