I am getting following error when I use bulk insert command.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 477, column 1 (Examid).
BULK INSERT FileImportDB..Exam FROM
'F:\70-431 Material\Practice Files\Chapter 10\ExamImportFile.txt' --WITH (TABLOCK )
with (FIRSTROW = 1
,ROWS_PER_BATCH = 5
There are only 500 rows in the file. Amazingly one one row, where the error occurred, is not inserted. According to BOL the batch should be rolled back and later batches should not be executed.
BOL: "Each batch of rows is inserted as a separate transaction. If, for any reason, the bulk-import operation terminates before completion, only the current transaction is rolled back. For instance, if a data file has 1000 rows, and a batch size of 100 is used, Microsoft SQL Server logs the operation as 10 separate transactions; each transaction inserts 100 rows into the destination table. If the bulk-import operation terminates while loading row 750, only the previous 49 rows are removed as SQL Server rolls back the current transaction. The destination table still contains the first 700 rows.
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.