Sql-server – BULK INSERT fails and rollback or not

sql serversql-server-2005

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!

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.

Related Topic