multi insert transactions

Posted on 2011-04-28
Last Modified: 2012-06-22

I am using a multi insert transaction to enter data in batches it there any benefit i changing this:

NSERT table (field1,field2) VALUES (data1,data2);
INSERT table (field1,field2) VALUES (data3,data4);
INSERT table (field1,field2) VALUES (data5,data6);
INSERT table (field1,field2) VALUES (data7,data8);
INSERT table (field1,field2) VALUES (data9,data10);

to this

INSERT table (field1,field2) VALUES
(data1,data2), (data3,data4), (data5,data6), (data7,data8), (data9,data10);

The latter method is generally quicker without a transcation but is it still quicker whilst using a transaction?

MAny thanks
Question by:andieje
    LVL 24

    Accepted Solution

    In this case, the transaction offers no benefit assuming all of your data is valid. If one of your inserts fails, you will not be able to detect it easily


    and also

    As far as speed is concerned, you pay a premium for transactions, and if the data is very likely to be good, I would use the mulit-insert without a transaction.


    Author Closing Comment

    thank-you - I'm sure you recognised your advice to me in this question from a previous question :)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now