当导入文件每行字段数不符时，能否取消全VARCHAR字段表的BULK INSERT操作？

你遇到的问题确实是SQL Server BULK INSERT的一个默认行为：当字段数和目标列数不匹配时，它只会默默忽略多余字段或补NULL，这种情况不算“错误”（比如数据类型转换失败、约束违反才会被计入错误计数），所以MAXERRORS=0根本不会触发，导致错误行错位插入，事务也正常提交。

下面给你几个可行的解决方案，按推荐程度排序：

方案1：用格式化文件强制校验字段数 #

这是最直接高效的方式，通过XML格式化文件明确指定每一行必须包含5个字段，只要行的分隔符数量不对，BULK INSERT就会抛出错误，触发TRY/CATCH块回滚事务。

步骤1：创建格式化文件 #

新建一个format.xml文件，内容如下：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="255"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="255"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="255"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="255"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="0x0a" MAX_LENGTH="255"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Column1" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Column2" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Column3" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Column4" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Column5" xsi:type="SQLVARCHAR"/>
 </ROW>
</BCPFORMAT>

把这个文件放到共享文件夹（和数据文件同目录即可），确保SQL Server服务账号有读取权限。

步骤2：修改BULK INSERT代码 #

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
 ,@formatFile SYSNAME
 ,@SQL_BULK VARCHAR(1000)
SELECT @dir = '\\sharedfolder\'
 ,@fname = 'testOrder.txt'
 ,@formatFile = '\\sharedfolder\format.xml' -- 格式化文件路径
SET @SQL_BULK = 'BULK INSERT #TempStage FROM ''' + @dir + @fname + '''
WITH (
 FIRSTROW = 1,
 DATAFILETYPE=''char'',
 FORMATFILE = ''' + @formatFile + ''',
 KEEPNULLS,
 MAXERRORS = 0
)'
BEGIN TRY
 BEGIN TRANSACTION
 EXEC (@SQL_BULK)
 COMMIT TRANSACTION
 PRINT '数据导入成功'
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0
  ROLLBACK TRANSACTION
 PRINT '导入失败：' + ERROR_MESSAGE()
END CATCH
SELECT * FROM #TempStage
DROP TABLE #TempStage

现在只要有行的字段数不是5，就会抛出类似“无法处理列X的数据”的错误，TRY/CATCH会捕获并回滚，不会插入任何错误数据。

方案2：先导入到单列表，再校验拆分 #

如果不想维护格式化文件，可以先把所有行导入到一个只有一列的临时表，然后校验每一行的分隔符数量（5个字段需要4个|），只要有不符合的就终止操作，否则再拆分到目标表。

示例代码：

CREATE TABLE #RawData (RawLine VARCHAR(MAX) NULL)
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 #RawData FROM ''' + @dir + @fname + '''
WITH (
 FIRSTROW = 1,
 DATAFILETYPE=''char'',
 ROWTERMINATOR = ''0x0a'',
 KEEPNULLS,
 MAXERRORS = 0
)'

BEGIN TRY
 BEGIN TRANSACTION
 EXEC (@SQL_BULK)

 -- 检查是否有行的字段数不符合要求（5个字段需要4个分隔符）
 IF EXISTS (
  SELECT 1
  FROM #RawData
  WHERE LEN(RawLine) - LEN(REPLACE(RawLine, '|', '')) != 4
 )
 BEGIN
  THROW 50001, '存在字段数不符的行，导入终止', 1;
 END

 -- 拆分数据到目标表（用PARSENAME刚好适配5个字段）
 INSERT INTO #TempStage (Column1, Column2, Column3, Column4, Column5)
 SELECT
  PARSENAME(REPLACE(RawLine, '|', '.'), 5),
  PARSENAME(REPLACE(RawLine, '|', '.'), 4),
  PARSENAME(REPLACE(RawLine, '|', '.'), 3),
  PARSENAME(REPLACE(RawLine, '|', '.'), 2),
  PARSENAME(REPLACE(RawLine, '|', '.'), 1)
 FROM #RawData

 COMMIT TRANSACTION
 PRINT '数据导入成功'
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0
  ROLLBACK TRANSACTION
 PRINT '导入失败：' + ERROR_MESSAGE()
END CATCH

SELECT * FROM #TempStage
DROP TABLE #RawData
DROP TABLE #TempStage

这种方式更灵活，还能添加额外的校验逻辑（比如检查行首标识是否合法），但性能比格式化文件稍差，适合数据量不大的场景。

补充说明：为什么MAXERRORS=0没用 #

SQL Server的MAXERRORS统计的是严重错误，比如数据类型转换失败、违反约束、行格式损坏等。而字段数不匹配时，BULK INSERT默认是“宽松”处理：多余字段直接忽略，缺少的字段补NULL，这种行为被视为“合法”，不会计入错误计数，所以MAXERRORS=0不会触发终止。

内容的提问来源于stack exchange，提问作者Jack Morris