Post reply

Insert 8 million records from text file to a table

  • August 18, 2007 at 8:13 am

    #727820

    You bet... just remember that the "Simple" recovery mode will NOT allow you to recover a damaged DB to a point in time.  The only difference between the 3 recovery mode for the import was the impact it had on the log file.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 20, 2007 at 4:40 pm

    #728152

    The best performance will come from BULK INSERT followed closely by BCP in most cases.  But "What is Best" is in the eyes of the beholder... If you need the blazing speed of BULK INSERT, then that might be the best.  If you need to be able to squirrel bad rows into a holding file for analysis and correction and still have some great speed, then BCP might be your best bet.  If you don't have folks that know either or, for some reason, think you need a VB script or active-x component to run your import, or you just happen to like GUI representations of what you're doing, then DTS might be your best bet.

    Truthfully, I've not written any scheduled imports with DTS so I don't know if the BULK INSERT in DTS is the same as the T-SQL BULK INSERT... all I know is that (so far), any of the guys at work that thought they could write something in DTS that would beat T-SQL BULK INSERT or even BCP have been wrong.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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