Post reply

BULK INSERT

  • January 27, 2012 at 1:06 am

    #263461

    How can i implement BULK INSERT wit Batch size.

    I dont have idea to implementing script. I have regular script which is causing growing log file size.

    I wants to modify the same script with BULK Insert with specifying Batch size .

  • January 27, 2012 at 1:20 am

    #1439182

    What is recovery model of your database? if it is full then you have to change it to bulk logged recovery.

  • January 27, 2012 at 2:00 am

    #1439191

    Have a look in SQL Server Books online (BOL)... it's all there.

    BULK INSERT

    [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]

    FROM 'data_file'

    [ WITH

    (

    [ [ , ] BATCHSIZE = batch_size ]

    [ [ , ] CHECK_CONSTRAINTS ]

    [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]

    [ [ , ] DATAFILETYPE =

    { 'char' | 'native'| 'widechar' | 'widenative' } ]

    [ [ , ] FIELDTERMINATOR = 'field_terminator' ]

    [ [ , ] FIRSTROW =first_row ]

    [ [ , ] FIRE_TRIGGERS ]

    [ [ , ] FORMATFILE = 'format_file_path' ]

    [ [ , ] KEEPIDENTITY ]

    [ [ , ] KEEPNULLS ]

    [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]

    [ [ , ] LASTROW = last_row ]

    [ [ , ] MAXERRORS = max_errors ]

    [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]

    [ [ , ] ROWS_PER_BATCH = rows_per_batch ]

    [ [ , ] ROWTERMINATOR = 'row_terminator' ]

    [ [ , ] TABLOCK ]

    [ [ , ] ERRORFILE = 'file_name' ]

    )]

  • January 27, 2012 at 1:32 pm

    #1439604

    BATCHSIZE controls the number of rows committed to the DB at one time.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • January 29, 2012 at 12:30 pm

    #1439769

    In addition to BATCHSIZE there are few more conditions for minimal log to occur. For example, TABLOCK option also should be used.

    Please the BOL for more details.

    Also note that, it (BULK LOGGED recovery model) my keep you log file size smaller, but your Log backup would NOT be smaller as it would contain the changed data pages.

  • March 7, 2012 at 11:58 am

    #1456170

    Hi,

    I am new and recently started working with this bank. I have this task of uploading quite a few csv files to the database. I need to upload all of them. Earlier my approach was to converge all of the csvs into one big csv (since the format of the files is same) and bulk upload it but due to permission issues i have been asked to upload it in batch.

    I havent used batch ever also what wud be the best way to approach it? should i still upload one big csv in batches or is there something which will upload with different batch size for different files.?

  • March 8, 2012 at 10:04 am

    #1456656

    My recommendation is, test the different approaches in a test servers. Finally select one of them which suits your environment.

  • March 8, 2012 at 10:08 am

    #1456661

    so the latest development has been that i have to do batch inserts on the fly no intermediary csv file. can anyone send me how to go about it or any online link, some book... any help would be appreciated,

  • March 8, 2012 at 10:24 am

    #1456677

    By "on the fly no intermediary csv file" do you mean directly from the source to SQL Server?

    What is the source?

    Import/Export wizard or SSIS may be the suitable tool for this purpose.

  • March 8, 2012 at 10:29 am

    #1456683

    So i have these CSV files and I have to write a c++ program to upload the data, I started with inserting the file by row by row which is taking alot of time like in hours. Then I thought of bulk loading but that has permission issues.

    what i want is to read the data from the file, have it in memory and upload it in chunks/batches directly to the sqlserver database, memory isnt a problem since the systems are pretty powerful.

  • March 8, 2012 at 10:42 am

    #1456694

    Inserting row by row is certainly not efficient.

    Have you tried BCP? It is the most efficient tools for inserting large number of rows into SQL Server.

    You can sort out the permission issues.

  • March 8, 2012 at 10:55 am

    #1456703

    I am not sure if there is a classic-C++ class already built for you to pickup and use but Bulk Copy is freely available within the SQL Server Native Client as a documented API: http://msdn.microsoft.com/en-us/library/ms130922.aspx

    If you are using C++ with the CLR you can leverage the SqlBulkCopy class included in the BCL (since 2.0).

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 8, 2012 at 11:16 am

    #1456716

    Thanks, I really appreciate your help.

    I will see if i can use this.

Viewing 13 posts - 1 through 12 (of 12 total)

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