Post reply

Ignore First row in data file in bulk insert

  • February 17, 2015 at 2:21 am

    #294696

    Hello

    I use bulk insert to fill a table

    I use code bellow

    bulk insert dbo.test

    from 'c:\test.txt'

    with(FIRSTROW=2,FORMATFILE='c:\test.xml'

    go

    but data inserted to table start from 3throw.

    Could you help me?

  • February 17, 2015 at 4:07 am

    #1778008

    1. check your first row is separated by the same row separator as all other

    2. from https://msdn.microsoft.com/en-gb/library/ms188365(v=sql.105).aspx: the FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • February 17, 2015 at 5:36 am

    #1778037

    I know the page says that FIRSTROW isn't intended to skip column headers, but I do it a lot.

    I suppose an alternate approach would be to bulk insert the whole file into your staging table and then delete the row you don't want.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

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

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