Post reply

Bulk Insert

  • February 9, 2011 at 9:14 am

    #244918

    Hi all,

    I have recently started a new job and have been tasked with running a Bulk Insert into a table (to save entering lines one at a time from the user interface).

    It'll be my first attempt and wondered if there were things I needed to do before I go ahead and run the insert ie. remove and rebuild indexes, think about any triggers on the table etc??

    Can someone help me with a Bulk Insert basics maybe???

    thanks, spin.

  • February 15, 2011 at 9:05 am

    #1287129

    a little more imformation please

    such as what type of file,how many records

  • February 16, 2011 at 8:49 am

    #1287733

    Have you read the Books Online Docs about bulk insert methods?

    http://msdn.microsoft.com/en-us/library/ms187042.aspx

    If you are wanting to bulk insert from a C# application this blog entry might help...

    http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

  • February 17, 2011 at 12:55 am

    #1288063

    I use bulk inserts only on tables with more than 10 million rows. SSIS handles the data import quite well I there is only 1 case where I use bulk inserts.

    If you are talking about data which comes from a front end where the users would have captured it, I would think that the number of records are going to be only a few thouand a day? In this case bulk insert would be a total over kill and you lose the nice front end of SSIS.

    If you really need to use bulk insert, I found that it sometimes is a bit of a shelp to get it 100% correct and running.:-D

    Just to add, if you are adding only a few thousand records, keep the indexes and just do the bulk insert, there should be no problem with bulk insert even on a slow PC

  • February 17, 2011 at 3:40 am

    #1288126

    Thanks for the replies.

    OK, yes i am only inserting a couple thousand rows at a time so BULK INSERT is overkill?

    Do i need to spend months learning SSIS to set up the import that way as i've not really experimented with VS yet?? shall i just stick with the BULK INSERT?

    Thanks, spin.

  • February 17, 2011 at 4:03 am

    #1288129

    For me personally, the bulk insert would be a bit of an overkill. I load daily data and somedays I would have to load upto a 10 million records, but even there I stay away from the bulk insert (for other reasons).

    I prefer to keep all my packages the same; where ever possible and with in reason; and if I use the bulk insert for instance, I would document it well and give the test results, why I used something different from the my standard packages.

    I do however use the bulk insert to export data from my database to a backup system on a daily basis. This is about 5 tables with each of a 10 miilion records. I found that SSIS with a data flow task is not that great with the export of the data over a linked server where as the bulk insert complete the same task in a few mins.

    Also use what you know. If its bulk insert, then go for it.

    And SSIS is actually very each to learn, there was 1 or 2 things that caught me in the beginning, but I've got a full data warehouse system running from SSIS & Stored procedures to do the more complex work, and a lot of it was on the job training in any case.

    If you have knowledge of another ETL tool, then you'll find SSIS very easy.

  • February 18, 2011 at 7:46 am

    #1288857

    thanks

    I am now getting stuck into SSIS 🙂

  • February 27, 2011 at 4:22 pm

    #1292087

    george.frewin (2/18/2011)

    thanks

    I am now getting stuck into SSIS 🙂

    To put a different slant on it, I think that SSIS is overkill. 😉 A nice, short, sweet BULK INSERT does the job well for any size import.

    Regardless of what you use, I'd like to suggest that you NEVER (yes, I said NEVER 🙂 ) import directly into a final table. Too much can go wrong. My recommendation is to always import into a staging table, do any and all data cleansing/validations, and then insert (or merge, if required) the data into the final table.

    --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 8 posts - 1 through 7 (of 7 total)

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