Multiple SQL Inserts in 1 Query

If your MySQL version is 3.22.5 or later, you can now INSERT multiple values / records in one single query. In most cases this is probably not useful but I recently found this out while trying to optimise my GIDTopsites script.

I had a function in my PHP script that empties a given MySQL table daily and automatically populates the empty table with a summary of ‘stats’ with data off another database table. Though not quite the same, my sample below will give you a vague idea how I eventually optimised my script using multiple inserts. First, a sample empty table to start with:

TABLE: beautiful
=================
id  | name   | age
----+--------+---
    |        |

As usual, the id field is an auto-increment field and our PHP script will just INSERT multiple values for the name and age columns (fields).

Multiple values in a single INSERT query example

In our sample PHP code below, we will INSERT 5 different records using just one MySQL INSERT query:


// Our MySQL query

$sql = "INSERT INTO beautiful (name, age)
 VALUES
 ('Helen', 24),
 ('Katrina', 21),
 ('Samia', 22),
 ('Hui Ling', 25),
 ('Yumie', 29)";

mysql_query( $sql, $conn );

That’s it! Assuming you had a MySQL table named `beautiful` all set up, you could save the sample script above and run it; and each run of the script will insert the five records at the same time every time!

Like I said, it’s wonderful to use something like this when you fill-up a certain MySQL table with (a summary of) data off other tables or an array.

The MySQL documentation randomly states this, under the Other Optimization Tips:

Use the multi-line INSERT statement to store many rows with one SQL command (many SQL servers supports this).

2 thoughts on “Multiple SQL Inserts in 1 Query

    1. eNviL

      I mean, instead of manually keying it in, because my website is able to add additional row for user to add more additional information but whenever it submits, it only insert the latest value instead of all the values.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>