Post reply

Ignoring error row in Bulk Insert

  • October 27, 2010 at 1:10 am

    #230375

    HI - I have a table in below format.

    create table family(id int, name varchar(100))

    go

    I have a text file('C:\family.txt') with below data which.

    1Husband

    2Wife0

    hikid

    Column delimiter is 'Tab'. My requirement is to insert the file data ignoring the error row and insert it to error file. I ran below command

    BULK INSERT family

    from 'C:\family.txt'

    with(ERRORFILE = 'c:\error.txt',MAXERRORS = 0 )

    However, It has given below error without inserting valid 2 records to table

    .Net SqlClient Data Provider: Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (id).

    .Net SqlClient Data Provider: Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    .Net SqlClient Data Provider: Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    Is there any way that I can insert valid records even there are invalid records exist in a file.

    thanks


    erajendar

  • October 27, 2010 at 4:03 am

    #1242731

    You specified MAXERRORS = 0, so you don't allow errors during import.

    BOL states:

    MAXERRORS = max_errors

    Specifies the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If max_errors is not specified, the default is 10.

    Raise your MAXERRORS and everything should work fine.

    -- Gianluca Sartori

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply