Export 10 million rows 46 GB of data to Text??

  • Help!  I need to know what is the most efficient and

    fast way of exporting 10 million records that has lots of

    BLOB into either a Text file or Excel or any other useable

    non-SQL data files!

    We are exporting this from our production database and

    must not lock up our tables.

    I was thinking of either BCP or DTS, but any other faster

    better method?  tips? anyone?

     

    thanks

    JON

    DBA/OLAP Developer -
    San Francisco, CA

  • This was removed by the editor as SPAM

  • BCP Out is the fastest way of exporting to a text file according to MS. If you can lock the table with 'table lock on bulk load' parameter of sp sp_tableoption, then it will be much faster.

    If also needing to import, I would choose bulk insert from the text file.

     

    If you need help with bcp, please post your problems here.

  • Thanks guys,

    I do have requirement of not to Lock the table while exporting the data.  Can I be able to do BCP in batches?   I would like each batch to export just 1 million records instead of 10 millions at once.

    Any thoughts of how BCP can do this like DTS where you set the batch size?  Would exporting impact usage to that table?

    If not, what can I do to prevent that.

    Thanks

    JON

    DBA/OLAP Developer -
    San Francisco, CA

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

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