Insert scripts takes so much time ? Any alternative.

  • I am creating and loading data through scripts scripts.

    There are two tables which are having records around 2lacs and 45 lacs.

    For these two tables I have created insert scripts having format as follows,

    Insert into Table_name (Column1,column2,...) values(value1,value2,...)

    The problem running these scripts is they are consuming so much time,nearly 3 to 4 hours.

    I have tried the option of bulk insert but it is not producing the exact data that is in scripts.

    So can any one tell If I use ,

    Insert into Table_name (columns...)

    select value1,value2....

    union all

    select value1,value2....

    union all

    select value1,value2....

    will the performance get better or same as the scripts.

    If any other option is there please give the suggestion.

  • You can use the script below...

    insert into

    select column1,column2... from

  • you can use the below

    insert into

    select column1,column2 from

  • barunkmallick (1/9/2009)


    you can use the below

    insert into

    select column1,column2 from

    Don't do double post just edit the post!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • No use .....it gives approximately same performance. Please read question carefully. I need to insert 45 lacs records in a table and i am having insert script of it.It takes approx. more than 5 hours.

    I want to make it fast.

    Any other way to have that data in the table through scripts.

  • Yes! below query will perform better as this will be bulk insert

    Insert into Table_name (columns...)

    select value1,value2....

    union all

    select value1,value2....

    union all

    select value1,value2....

    -Vikas Bindra

  • Performance will improve using UNION ALL, as you mention in your first post. Of course, always test first.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Try dropping the indexes before inserting and recreate the indexes after insert

    and insert using Select unionall option

    -Vikas Bindra

  • I'm confused as to why BULK INSERT is not generating the correct data. It's generally going to work better than straight inserts just because of the difference in logging. Any more details there?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes you are right.

    I have tried the BULK INSERT by using .csv file it is working correct.

    Because i have not specified the KEEPIDENTITY attribute it was giving incorrect result.

    Thank you .

  • rtbhosale (1/9/2009)


    Yes you are right.

    I have tried the BULK INSERT by using .csv file it is working correct.

    Because i have not specified the KEEPIDENTITY attribute it was giving incorrect result.

    Thank you .

    Excellent, glad to hear it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • rtbhosale (1/9/2009)


    Yes you are right.

    I have tried the BULK INSERT by using .csv file it is working correct.

    Because i have not specified the KEEPIDENTITY attribute it was giving incorrect result.

    Thank you .

    So how much better did your performance get?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Less than 5 minutes approximately 45 lacs records inserted into database.

    Extremely fast.

  • Great. Thanks for the feedback.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • once again

    lakhs is a large measure - I think it is from the Hindi

    por favor (please, in Spanish)

    if you use a non-english term, please define it

    dank U (Dutch)

Viewing 15 posts - 1 through 14 (of 14 total)

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