1

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!

1

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.

  • I'm still not sure about this because after the first failed bulk insert reported above I did it again, and I got the same error at the same line. If previous lines were already inserted, I would have received a duplicate key error at the first line inserted. – Danilo Brambilla Sep 16 '10 at 13:24
  • About T-SQL BULK INSERT command MSDN says: ... BATCHSIZE =batch_size Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch. For information about performance considerations, see "Remarks," later in this topic. ... msdn.microsoft.com/en-us/library/ms188365.aspx So I assume it is transactional in the end. – Danilo Brambilla Oct 14 '10 at 8:29
  • But later on it says "The BULK INSERT statement can be executed within a user-defined transaction. Rolling back a user-defined transaction that uses a BULK INSERT statement and BATCHSIZE clause to import data into a table or view using multiple batches rolls back all batches sent to SQL Server.". I'll test this myself today if I get some spare time. We seem to have conflicting reports. – Sean Howat Oct 14 '10 at 14:13
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.