BULK INSERT and ROLLBACK

25 November,2014 by Jack Vamvas

BULK INSERT imports a data files into SQL Server. It’s  range of individual INSERT statements. If the BULK INSERT job fails, there is no ROLLBACK from the commited INSERTS.

Use BULK INSERT with a UDF

One workaround to this problem is to place the BULK INSERT into a user defined transaction. Use this pattern to manage a BULK INSERT process.  This can be further enhanced with  try...catch blocks. Trap errors and report into a log file

 

BEGIN TRANSACTION
BEGIN TRY
BULK INSERT  MyTable 
FROM 'e:\MyTable.txt' 
WITH (FORMATFILE = 'e:\MyFormatFile.Fmt');
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

Read More on managing Bulk Data

Data Loading Performance Guide

SQL Server - Diary of a DBA – Num 6 – Agnostic Technology

SQL Server architecting write-intensive databases


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on BULK INSERT and ROLLBACK


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer