Insert multiple rows with one query MySQL

Insert multiple rows with one query MySQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Batch Inserting Rows: Beyond Simple Loops

The requirement to insert a specific number of rows based on a user-provided quantity input is a common pattern, often triggered by bulk data entry or generating test records. The proposed solution in older documentation typically involves wrapping a single-row INSERT statement inside a loop that runs from 1 to the requested quantity. While this approach seems intuitive, relying on procedural loops with raw queries introduces significant overhead and security vulnerabilities that modern PHP stacks should avoid.

Consider the legacy snippet provided:

mysql_query("INSERT INTO `pxlot` (realname,email,address,phone,status,regtime,ip) 
 VALUES ('$realname','$email','$address','$phone','0','$dateTime','$ip')")
 or die (mysql_error());

This code uses the deprecated mysql_* extension and constructs the SQL string by directly interpolating variables. Running this inside a PHP loop to insert $N$ rows forces the database engine to parse and execute the query $N$ times. More critically, every iteration exposes the application to SQL injection if $realname, $email, or other inputs are not sanitized individually. In a production environment, executing hundreds of individual queries can also exhaust connection pools and delay response times due to network round-trips for each statement.

The most efficient alternative is using INSERT ... SELECT. This single query constructs the target row in memory by selecting from a derived table or a temporary structure, then inserts that result set into the destination table. For the specific scenario where you need $N$ identical rows (or rows with slightly varying timestamps), you can generate the data source dynamically and pass it to one INSERT statement.

Here is how you might implement this using PDO, which supports prepared statements and handles the bulk insert efficiently:

use PDO;

$quantity = (int)$inputQuantity; // Ensure quantity is an integer
$realname = $pdo->quote($realname);
$email = $pdo->quote($email);
// ... quote other static fields

$sql = "INSERT INTO `pxlot` 
        (realname, email, address, phone, status, regtime, ip)
        SELECT ?, ?, ?, ?, '0', DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3600) SECOND), ?
        FROM (SELECT UNNEST(GENERATE_SERIES(1, ?)) AS idx) AS dummy";

// Note: Implementation of GENERATE_SERIES or UNNEST depends on the specific MySQL/MariaDB version.
// In older MySQL versions without window functions, a CTE or subquery mimicking a loop is required.

If you are targeting an older MySQL version that lacks LATERAL views or recursive CTEs for generating series, you can still achieve bulk insertion by building the value list programmatically before executing the statement. However, even in those cases, constructing one large query is vastly superior to looping.

When dealing with varying timestamps (like $dateTime), you must ensure that each inserted row has a unique regtime. In the legacy loop approach, this was often handled by incrementing a counter or appending milliseconds inside the loop. With a INSERT ... SELECT strategy, you can generate these unique timestamps in a subquery without touching the application loop logic.

Security and Modern Stack Considerations

The original code snippet relies on string concatenation for SQL construction ('$realname'). This is the primary vector for SQL injection attacks. Regardless of whether you use a loop or a batch insert, if the input variables are not bound correctly, the database becomes compromised. The INSERT ... SELECT method allows you to bind all variable columns as parameters in a single execution, ensuring that user input never touches the query string directly.

Additionally, the source material references the mysql_* functions, which were deprecated in PHP 5.5 and removed entirely in PHP 7.0. Migrating from these functions to PDO or MySQLi is not just a version upgrade; it is a necessary step to enable prepared statements, which are mandatory for secure batch operations.

For projects built on the Laravel framework, handling this logic is even more straightforward. The DB::table methods and Eloquent collections provide native support for bulk insertion without writing raw SQL or managing loops manually. If you need to insert multiple identical records, you can simply duplicate a model instance or create a collection with generated variations.

Trade-offs

While INSERT ... SELECT is highly performant, it does have limitations. It requires the database engine to handle the row generation internally, which means complex logic (like calculating specific hash values based on previous rows) cannot be easily pushed into the SQL query unless you write stored procedures. However, for standard scenarios involving timestamps and static fields, this approach offers a significant reduction in execution time and network latency compared to procedural loops.

Our network