Является ли пакетная вставка SQL Server транзакционной?

Если я выполню следующий запрос в анализаторе запросов SQL Server 2000:

BULK INSERT  OurTable 
FROM 'c:\OurTable.txt' 
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWS_PER_BATCH = 10000, TABLOCK)

В текстовом файле, который соответствует схеме OurTable для 40 строк, но затем меняет формат для последних 20 строк (допустим, в последних 20 строках меньше полей), я получаю сообщение об ошибке. Однако первые 40 строк сохраняются в таблице. Есть ли что-то в том, как я вызываю массовую вставку, что делает ее не транзакционной, или мне нужно сделать что-то явное, чтобы заставить ее откатиться в случае сбоя?


sql-server transactions bulkinsert
person Brian    schedule 03.09.2008    source источник

Ответы (4)


arrow_upward
25
arrow_downward

BULK INSERT действует как серия отдельных INSERT операторов и, таким образом, в случае сбоя задания оно не откатывает все зафиксированные вставки.

Однако его можно поместить в транзакцию, чтобы вы могли сделать что-то вроде этого:

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
person Josef    schedule 30.09.2008
comment
будьте осторожны с заполнением журнала транзакций и т. д., если вы вставляете МНОГО строк. - person Ian Ringrose; 26.01.2010

arrow_upward
3
arrow_downward

Можно откатить вставки. Для этого нам нужно сначала понять две вещи

BatchSize

: Количество строк, которые нужно вставить за транзакцию. По умолчанию используется весь файл данных. Итак, файл данных находится в транзакции

Скажем, у вас есть текстовый файл с 10 строками, а строка 8 и строка 7 содержат недопустимые данные. При массовой вставке файла без указания или с указанием размера пакета 8 из 10 вставляются в таблицу. Недопустимая строка, то есть 8-я и 7-я, терпят неудачу и не вставляются.

Это происходит из-за того, что по умолчанию MAXERRORS счетчик равен 10 на транзакцию.

Согласно MSDN:

МАКСЕРРОС:

Задает максимальное количество синтаксических ошибок, допустимых в данных до отмены операции массового импорта. Каждая строка, которую невозможно импортировать с помощью операции массового импорта, игнорируется и засчитывается как одна ошибка. Если max_errors не указан, значение по умолчанию - 10.

Таким образом, чтобы не выполнить все 10 строк, даже если одна из них недействительна, нам нужно установить MAXERRORS=1 и BatchSize=1. Здесь также имеет значение количество BatchSize.

Если вы укажете BatchSize, а недопустимая строка находится внутри определенного пакета, он откатит только конкретный пакет, а не весь набор данных. Так что будьте осторожны при выборе этого варианта

Надеюсь, это решит проблему.

person Sai Bhasker Raju    schedule 22.12.2016
comment
В чем смысл BatchSize = 1 вместо отдельных операторов INSERT? - person proteus; 25.01.2018
comment
Я думаю, что размер пакета = 1 совершает транзакцию, которая аналогична отдельным операторам вставки, но для более поздних требуется несколько номеров цикла и строк для вставки без дублирования. Согласны с вашим утверждением, но это можно сделать с помощью атрибутов BULK INSERT - person Sai Bhasker Raju; 29.01.2018

arrow_upward
1
arrow_downward

Как указано в определении BATCHSIZE для BULK INSERT в библиотеке MSDN (http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx):

«Если это не удается, SQL Server фиксирует или откатывает транзакцию для каждого пакета ...»

В заключение нет необходимости добавлять транзакционность в Bulk Insert.

person Guillermo Garcia    schedule 01.03.2012

arrow_upward
0
arrow_downward

Попробуйте поместить его в пользовательскую транзакцию и посмотрите, что произойдет. На самом деле он должен откатиться, как вы это описали.

person kaiz.net    schedule 03.09.2008