Finding out problematic data in Bulk Insert data truncation error
Suppose you want to import data from a text file to a table, one of the options is to use Bulk Insert command
It allows to import data to the table, though one/more columns don't have enough size to get the maximum length of data from text file.It throws errors about data truncation with line numbers.
What if you want to find out the rows from the text file that are not imported
Here is a way
Consider the following example
--Create a testing table
create
table bulk_test
(
test_no int,
test_name varchar(10)
)
GO
--The text file has the following data
id, name
23, test
8, kjhasd
87, sdf
23, testing column
99, ujh
45, this is for testing only
--Bulk import query
BULK
INSERT bulk_test
FROM 'e:\test.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
firstrow=2
)
--Error that is thrown
Msg 4863
, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 5, column 2 (test_name).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 7, column 2 (test_name).
--Finding out the data that causes the error
select
t1.*
from
openrowset('Microsoft.Jet.OLEDB.4.0','text;HDR=YES;FMT=FixedLength;Database=e:\', test#txt) as t1
left join bulk_test as t2 on t1.id=t2.test_no and t1.name=t2.test_name
where t2.test_no is null