Home / server / Questions / 179666
Accepted
Danilo Brambilla
Asked: 2010-09-10 11:47:24 +0800 CST

BULK INSERT fails and rollback or not?

  • 772

while importing a csv data file with a very basic BULK INSERT statement on a SQL 2005 database server, I received some format errors and then a duplicate key error:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8625, column 6 (min_prc).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8627, column 6 (min_prc).
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint [TABLE]. Cannot insert duplicate key in object [TABLE].
The statement has been terminated.

Has the entire operation failed without actually have inserted any record? Have the records before the duplicate key error been inserted?

Can anyone point me to a website/document that describes bulk insert actions in case of errors?

Thank you all for help!

sql-server sql-server-2005

2 Answers

  1. Best Answer

    The Stack Overflow question, Is SQL Server Bulk Insert Transactional?, seems to answer your question.

    For reference, yes they have been inserted unless you manually entered the bulk insert into a user defined transaction with a rollback. Bulk insert treats each row as an individual insert.

    • 1

  2. You can rollback the inserts . To do that we need to understand two things first

    BatchSize

    : No of rows to be inserted per transaction . The Default is entire Data File. So a data file is in transaction

    Say you have a text file which has 10 rows and row 8 and Row 7 has some invalid details . When you Bulk Insert the file without specifying or with specifying batch Size , 8 out of 10 get inserted into the table. The Invalid Row's i.e. 8th and 7th gets failed and doesn't get inserted.

    This Happens because the Default MAXERRORS count is 10 per transaction.

    As Per 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.

    So Inorder to fail all the 10 rows even if one is invalid we need to set MAXERRORS=1 and BatchSize=1 Here the number of BatchSize also matters.

    If you specify BatchSize and the invalid row is inside the particular batch , it will rollback the particular batch only ,not the entire data set. So be careful while choosing this option

    Hope this solves the issue.

    • 1