关于事务:SQL Server批量插入是否具有事务性?

关于事务:SQL Server批量插入是否具有事务性?

Is SQL Server Bulk Insert Transactional?

如果我在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=1BatchSize=1。在这里,BatchSize的数量也很重要。

如果指定BatchSize并且无效行位于特定批次内,则它将仅回滚特定批次,而不回滚整个数据集。
因此,在选择此选项时要小心

希望这能解决问题。


如MSDN Library(http://msdn.microsoft.com/zh-cn/library/ms188365(v=sql.105).aspx)中的大容量插入的BATCHSIZE定义中所述:

"如果失败,SQL Server将为每个批处理提交或回滚事务……"

总之,不必向批量插入添加事务性。


尝试将其放入用户定义的事务中,看看会发生什么。实际上,它应该按照您的描述进行回滚。


推荐阅读