ejemplos - ¿Es SQL Server Bulk Insert Transactional?

ejecutar bcp desde sql server

Question

Si ejecuto la siguiente consulta en SQL Server 2000 Query Analyzer:

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

En un archivo de texto que se ajusta al esquema de OurTable para 40 líneas, pero luego cambia el formato de las últimas 20 líneas (digamos que las últimas 20 líneas tienen menos campos), recibo un error. Sin embargo, las primeras 40 líneas están comprometidas con la mesa. ¿Hay algo en la forma en que llamo Bulk Insert que no sea transaccional, o tengo que hacer algo explícito para forzar su reversión en caso de falla?

Answer 1

Como se indica en la definición BATCHSIZE para BULK INSERT en MSDN Library ( http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx ):

"Si esto falla, SQL Server confirma o retrotrae la transacción para cada lote ..."

En conclusión, no es necesario agregar transaccionalidad a Bulk Insert.

Answer 2

Intenta ponerlo dentro de la transacción definida por el usuario y ver qué pasa. En realidad, debería retroceder como lo describiste.

Answer 3

BULK INSERT actúa como una serie de INSERT individuales y, por lo tanto, si el trabajo falla, no revierte todas las inserciones comprometidas.

Sin embargo, se puede colocar dentro de una transacción para que pueda hacer algo como esto:

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

Answer 4

Puede deshacer las inserciones. Para hacer eso, necesitamos entender dos cosas primero

BatchSize

: No de filas para insertar por transacción. El valor predeterminado es todo el archivo de datos. Entonces, un archivo de datos está en transacción

Digamos que tiene un archivo de texto que tiene 10 filas y una fila 8 y la Fila 7 tiene algunos detalles no válidos. Cuando realiza un Bulk Inserta el archivo sin especificar o especificando el tamaño del lote, 8 de cada 10 se insertan en la tabla. La fila inválida, es decir, la 8ª y la 7ª, falla y no se inserta.

Esto sucede porque el recuento predeterminado de MAXERRORS es 10 por transacción.

Como por MSDN:

MAXERRORS:

Especifica la cantidad máxima de errores de sintaxis permitidos en los datos antes de que se cancele la operación de importación masiva. Cada fila que no se puede importar mediante la operación de importación masiva se ignora y se cuenta como un error. Si no se especifica max_errors, el valor predeterminado es 10.

Por lo tanto, para anular todas las 10 filas, incluso si una no es válida, debemos establecer MAXERRORS=1 y BatchSize=1 Aquí también importa el número de BatchSize.

Si especifica BatchSize y la fila no válida está dentro del lote en particular, solo retrotraerá el lote en particular, no todo el conjunto de datos. Así que ten cuidado al elegir esta opción

Espero que esto resuelva el problema.