Является ли пакетная вставка 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 строк закреплены за таблицей. Есть ли что-то в том, как я вызываю массовую вставку, что делает его не транзакционным, или мне нужно сделать что-то явное, чтобы заставить его откатиться в случае сбоя?
Ответы 4
Попробуйте поместить его в пользовательскую транзакцию и посмотрите, что произойдет. На самом деле он должен откатиться, как вы это описали.
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
Как указано в определении BATCHSIZE для BULK INSERT в библиотеке MSDN (http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx):
«Если это не удается, SQL Server фиксирует или откатывает транзакцию для каждого пакета ...»
В заключение нет необходимости добавлять транзакционность в Bulk Insert.
Можно откатить вставки. Для этого нам нужно сначала понять две вещи
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 из 10 вставляются в таблицу. Недопустимая строка, то есть 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, а недопустимая строка находится внутри определенного пакета, он откатит только конкретный пакет, а не весь набор данных. Так что будьте осторожны при выборе этого варианта
Надеюсь, это решит проблему.
В чем смысл BatchSize = 1 вместо отдельных операторов INSERT?
Я думаю, что размер партии = 1 совершает транзакцию, которая аналогична отдельным операторам вставки, но для более поздних версий требуются номера циклов и строк для вставки без дублирования. Согласны с вашим утверждением, но это можно сделать с помощью атрибутов BULK INSERT
будьте осторожны с заполнением журнала транзакций и т. д., если вы вставляете МНОГО строк.