当行的字段计数不正确时，您可以取消所有 VARCHAR 的批量插入吗？

Question

我使用BULK INSERT将分隔的.txt文件加载到一个有5列的暂存表中。.txt文件有时可能包含错误，并且每行的字段数多于或少于5个。如果发生这种情况，是否可以检测到并取消整个BULK INSERT？

每个表列的类型都是VARCHAR。之所以这样做，是因为标题(H01(行和行(L0101、L0102等…(包含不同类型的字段。因此，设置MAXERRORS=0似乎不起作用，因为从技术上讲没有语法错误。因此，事务被提交，catch块永远不会激活，回滚也不会发生。行仍然被错误地移位或聚束插入到表中。

Expected .txt file format:
H01|Order|Date|Name|Address
L0101|Order|Part|SKU|Qty
L0102|Order|Part||Qty            <-- Fields can be blank
L0103|Order|Part|SKU|Qty
Incorrect .txt file example:
H01|Order|Date|Name|Address
L0101|Order||Part|SKU|Qty        <-- Extra field in the middle
||L0102|Order|Part|SKU|Qty       <-- Extra fields at the beginning
L0103|Order|Part|SKU|Qty||       <-- Extra fields at the end

代码：

CREATE TABLE #TempStage (
Column1    VARCHAR(255) NULL
,Column2    VARCHAR(255) NULL
,Column3    VARCHAR(255) NULL
,Column4    VARCHAR(255) NULL
,Column5    VARCHAR(255) NULL
)
DECLARE 
@dir           SYSNAME
,@fname         SYSNAME
,@SQL_BULK      VARCHAR(255)
SELECT 
@dir           =   '\sharedfolder'
,@fname         =   'testOrder.txt'

SET @SQL_BULK = 
'BULK INSERT #TempStage FROM ''' + @dir + @fname + ''' WITH
(
FIRSTROW = 1,
DATAFILETYPE=''char'',
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0a'',
KEEPNULLS,
MAXERRORS = 0
)'
BEGIN TRY
BEGIN TRANSACTION
EXEC (@SQL_BULK)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
SELECT * FROM #TempStage
DROP TABLE #TempStage

预期输出：

第3列>第4列日期<1td>地址SKU数量空SKU数量

第1列	第2列		第5列
H01	订单	名称
L0101	订单	零件
L0102	订单	零件	数量
L0103	订单	零件

正如许多人之前所指出的：BULK INSERT很快，但不是很灵活，尤其是在列不一致的情况下。

当您的输入可能有错误的数据时(从技术上讲，从SQL的角度来看，这就是您所描述的(，您必须采用一种或多种不同的方法：

1. 预处理和"；"干净"；先用外部程序处理数据，或
2. BULK INSERT到一个有一个大VARCHAR(MAX(列的暂存表，然后自己用SQL解析和清理数据，然后将其移动到有实际列的表中，或者
3. 使用CLR代码/技巧有效地执行上述(1(和/或(2(，或
4. 编写一个外部程序以同时清理/预处理和SqlBulk将数据复制到SQL Server中(替换BULK INSERT(，或者
5. 改为使用SSMS(不过仍然很难处理坏列/变量列(

在我的职业生涯中，我曾经做过所有这些，它们都有点困难和耗时(工作很耗时，运行时间很长(。

我已经创建了一种基本的方法来做我需要的事情：

加载暂存表后，请检查"|"的任何实例(或使用的任何分隔符(，如果发现，则会引发错误。

IF EXISTS(SELECT * FROM #TempStage WHERE
Column1 LIKE '%|%'
OR Column2 LIKE '%|%'
OR Column3 LIKE '%|%'
OR Column4 LIKE '%|%'
OR Column5 LIKE '%|%'
)
RAISERROR('Incorrect file formatting; Pipe character found.', 16, 1);