问题描述
如果我在SQL Server 2000查询分析器中运行以下查询：
If I run the following query in SQL Server 2000 Query Analyzer:
BULK INSERT OurTable
FROM 'c:\OurTable.txt'
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWS_PER_BATCH = 10000, TABLOCK)
在符合OurTable模式的40行文本文件中，然后更改最后20行的格式（假设最后20行的字段较少），我收到一个错误。但是，前40行被提交到表中。我调用大容量插入的方式是否有些问题，使其不具有事务性，还是我需要做一些明确的事情来强制其在失败时回滚？
On a text file that conforms to OurTable's schema for 40 lines, but then changes format for the last 20 lines (lets say the last 20 lines have fewer fields), I receive an error. However, the first 40 lines are committed to the table. Is there something about the way I'm calling Bulk Insert that makes it not be transactional, or do I need to do something explicit to force it to rollback on failure?
推荐答案
批量插入充当一系列单独的 INSERT 语句，因此，如果
BULK INSERT acts as a series of individual INSERT statements and thus, if the job fails, it doesn't roll back all of the committed inserts.
但是可以将它放置在事务中，因此您可以执行以下操作：
It can, however, be placed within a transaction so you could do something like this:
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
这篇关于SQL Server批量插入是否具有事务性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！