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

Published Monday, October 19, 2009 11:07 AM by Madhivanan

Comments

No Comments