Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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