Home / server / Questions / 43705
Accepted
Sampson
Asked: 2009-07-22 11:06:08 +0800 CST

MySQL Query Browser to run 400+ Insert Statements?

  • 772

I'm manually duplicating a few records and would like to paste the INSERT statements directly into Query Browser, but I have only ever performed one INSERT at a time with QB. Is it possible to insert, and run, 400+ inserts one after another without having to manually initiate each?

mysql

3 Answers

  1. Best Answer

    Open a New Script Tab, located under File. Paste your inserts, and press Execute.

    • 1

  2. as you said, yes it's possible....but IMO if you have the 400+ insert statements, you'd be better off saving them into a plain text file and running them with the mysql command line client.

    that way, you get to use your favourite editor to create the file rather than a tiny little input window in the QB. Your editor will probably also have far better editing capabilities, including syntax highlighting.

    if the insert statements are all the same except for the actual data to be inserted, then you can put just the data in, say, a CSV file and write a simple perl/awk/php/whatever script to generate the SQL from that....then load that into your editor for any final edits. or you could use the LOAD DATA statement instead of individual INSERT statements.

    other advantages of using a text-file sql script are:

    • it gives you a final chance to look over and check the sql script before running it
    • you can run the sql script on a backup copy of the database first to verify that it's correct
    • the sql script is documentation - a record of what you did. you can put comments in it to explain why

    finally, if you're using a database engine that supports transactions (e.g. innodb) then remember to use them to ensure that either ALL inserts are successfully completed, or NONE are. that allows you to safely correct and re-run the sql script if there was an error.

    • 0

  3. The old MySQL Query Browser and MySQL Administrator have been replaced by MySQL Workbench.

    MySQL Workbench includes: - Model (replacing DBDesigner) - Query (replacing MySQL Query Browser) - Admin (replacing MySQL Administrator) - SSH-Tunnel (Remote Admin)

    MySQL Workbench discussions:
    - http://forums.mysql.com/index.php?151

    • 0