SQL Server 大容量插入是事务性的吗? [英] Is SQL Server Bulk Insert Transactional?

问题描述

如果我在 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 = '	', ROWS_PER_BATCH = 10000, TABLOCK)

在一个文本文件中，它有 40 行符合 OurTable 的架构，但随后更改了最后 20 行的格式(假设最后 20 行的字段较少)，我收到一个错误.但是，前 40 行已提交到表中.我调用 Bulk Insert 的方式有什么问题使它不是事务性的，还是我需要做一些明确的事情来强制它在失败时回滚?

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?

推荐答案

BULK INSERT 充当一系列单独的 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 = '	', 
   ROWS_PER_BATCH = 10000, TABLOCK)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

这篇关于SQL Server 大容量插入是事务性的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！