Insert multiple table rows at once

We have say a item that goes through multiple tests and over a day we have say 100 items.

I want to have a row in a table for each item under column date, Id, testA, testB, testC, comment.

Most of the time the data will be; date, “incremented number”, pass, pass, pass, none.

The user would then select to say enter 100 records. If everything passed that day no further action is required. If one or two items failed that day he could select that item by the id and amend a test to fail and enter the comment.

After the insert query what is the method to duplicate the rows or entry ‘x’ number of times ?

To save NULLS in the comments sections what is the best thing to enter for later on to perform a query on any item has a comment. Use 0 and query against greater then. Or use ‘none’ and a query against does not equal none?

It’s a MySQL database.


If you’re absolutely certain that the ID’s entered in will always be sequential, and never missing an index, you could set your MySQL column to NOT NULL AUTO_INCREMENT. Then you wouldn’t have to worry about supplying the ID.

To enter multiple default entries at once, just wrap your system.db.runUpdateQuery() call in a for-loop. Or generate a single INSERT query with multiple VALUE definitions seperated by a comma and run that. Or if speed is a concern, consider using LOAD DATA INFILE to insert a default block of data from a string tag or file.

However, if you perform the default row INSERTs before the operator makes any changes for the actual data, then you’ll also need to run the row UPDATE queries for each of their edits (non-default rows). You might instead want to have a daily data-entry screen that the operator can submit new parts from. You could give them numeric text field to enter the number of parts for the day, and the starting ID, then generate the table with default values. Then let them edit any values in the table (besides maybe date) and when they click “submit”, have a script that inserts each row into your DB table.