I need to use a bulk insert using format file. On incorrect data file I get the error - "There will be truncation error" But if I use bulk insert without the format file I get the exact row number of incorrect records. Why using a format file I dont get the row number of the incorrect rows.
Example: file1.txt has incorrect rows
BULK INSERT table1
FROM 'D:\data\file1.txt'
WITH
( FORMATFILE = 'D:\data\file1.fmt',
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
Error - Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.
Without format file -
BULK INSERT table1
FROM 'D:\data\file1.txt'
WITH
( DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 2, column 5