How to do a Bulk Insert when the text file has a header row

Posted on 2009-02-24
Last Modified: 2012-05-08
The Bulk Insert works if I remove the header row.
What is the syntax if I wish to ignore or skip the header row?

SET @sql = 'BULK INSERT SplitsWork from ''' + @SourcePath + @SourceFile + ''' ' +      


Open in new window

Question by:Dovberman
    LVL 142

    Accepted Solution


    Author Comment

    Thanks for the reference.

    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.

    I got the following error:
    Cannot bulk load. When you use the FIRSTROW and LASTROW parameters, the value for FIRSTROW cannot be greater than the value for LASTROW.

    I will include the header row, then delete it from the table.

    These changes work:

    SET @sql = 'BULK INSERT SplitsWork from ''' + @SourcePath + @SourceFile + ''' ' +      
                   'WITH (FIELDTERMINATOR  = ''\t'', ROWTERMINATOR= ''\n'',FIRSTROW=1) ';
    Resulting data after the insert.

    I can delete where field1 value = 'Symbol'
    Symbol      Date      Ratio
    CLM      20081223      1-4
    CRF      20081223      1-2
    DOY      20090130      4-1

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL HELP 2 65
    Duplicate + trim records SQL 11 39
    SQL PIVOT Table 21 30
    Simple IF evaluation mistake in SQL Azure statement 10 25
    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now