如果我在SQL Server 2000查询分析器中运行以下查询:
1 2 3
| BULK INSERT OurTable
FROM 'c:\OurTable.txt'
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWS_PER_BATCH = 10000, TABLOCK) |
在一个符合OurTable模式的40行文本文件中,但随后更改了最后20行的格式(假设最后20行的字段较少),我收到错误消息。 但是,前40行被提交到表中。 我调用批量插入的方式是否有些问题,使其不具有事务性,还是我需要做一些明确的事情来迫使它在失败时回滚?
BULK INSERT充当一系列单独的INSERT语句,因此,如果作业失败,它不会回滚所有已提交的插入。
但是,它可以放在事务中,因此您可以执行以下操作:
1 2 3 4 5 6 7 8 9 10 11
| BEGIN TRANSACTION
BEGIN TRY
BULK INSERT OurTable
FROM 'c:\OurTable.txt'
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t',
ROWS_PER_BATCH = 10000, TABLOCK)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH |
您可以回滚插入。为此,我们需要首先了解两件事
BatchSize
: No of rows to be inserted per transaction . The Default is entire
Data File. So a data file is in transaction
假设您有一个文本文件,该文件包含10行和8行,而7行包含一些无效的详细信息。批量插入文件时,如果未指定或指定批处理大小,则将十分之八插入表中。无效行的第8位和第7位失败,也没有插入。
发生这种情况是因为默认MAXERRORS计数是每个事务10。
根据MSDN:
MAXERRORS :
Specifies the maximum number of syntax errors allowed in the data
before the bulk-import operation is canceled. Each row that cannot be
imported by the bulk-import operation is ignored and counted as one
error. If max_errors is not specified, the default is 10.
因此,为了使所有10行都失败,即使其中之一无效,我们也需要设置MAXERRORS=1和BatchSize=1。在这里,BatchSize的数量也很重要。
如果指定BatchSize并且无效行位于特定批次内,则它将仅回滚特定批次,而不回滚整个数据集。
因此,在选择此选项时要小心
希望这能解决问题。
如MSDN Library(http://msdn.microsoft.com/zh-cn/library/ms188365(v=sql.105).aspx)中的大容量插入的BATCHSIZE定义中所述:
"如果失败,SQL Server将为每个批处理提交或回滚事务……"
总之,不必向批量插入添加事务性。
尝试将其放入用户定义的事务中,看看会发生什么。实际上,它应该按照您的描述进行回滚。