Example Codes for Inserting Multiple Rows in MySQL

Basic Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...),
       (value1, value2, ...),
       ...;

Explanation:

• INSERT INTO table_name: Specifies the target table where you want to insert the data.
• (column1, column2, ...): Lists the columns into which you'll insert values. This is optional if you're inserting values into all columns.
• VALUES (value1, value2, ...), (value1, value2, ...), ...;: Provides the values to be inserted for each row. Each set of values is enclosed in parentheses, and multiple sets are separated by commas.

Example:

Consider a table named customers with columns id, name, and email. To insert three new customers, you could use the following statement:

INSERT INTO customers (id, name, email)
VALUES (1, 'Alice', '[email protected]'),
       (2, 'Bob', '[email protected]'),
       (3, 'Charlie', '[email protected]');   

Key Points:

• Column Order: The order of values in the VALUES clause must match the order of columns in the INSERT INTO statement.
• Missing Columns: If you don't specify a value for a column, it will be assigned a default value (if one exists) or a NULL value.
• Data Types: Ensure that the values you provide match the data types of the corresponding columns in the table.
• Efficiency: For large numbers of rows, consider using a prepared statement or bulk insert methods provided by your MySQL driver or database tools.

Additional Considerations:

• Auto-Increment: If the id column is auto-incremented, you don't need to specify values for it. The database will automatically assign unique values.
• Duplicate Keys: If you try to insert a row with a duplicate primary key or unique constraint value, an error will occur.
• Error Handling: Use appropriate error handling mechanisms to catch and handle potential exceptions during the insertion process.

Example Codes for Inserting Multiple Rows in MySQL

Basic Example using PHP

<?php
$servername = "your_hostname";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error)    {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare the SQL statement
$sql = "INSERT INTO    your_table (column1, column2, ...) VALUES (?, ?, ...), (?, ?, ...), ...";

// Prepare the statement
$stmt = $conn->prepare($sql);

// Bind parameters
$stmt->bind_param("ssi", $value1, $value2, ...); // Replace "ssi" with appropriate data types

// Set parameters
$value1 = "value1";
$value2 = 123;
// ...

// Execute the statement
$stmt->execute();

// Check if the statement was successful
if ($stmt->affected_rows > 0) {
    echo "New records created successfully";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

Example using Python and MySQL Connector/Python

import mysql.connector

mydb = mysql.connector.connect(
  host="your_hostname",
  user="your_username",
  password="your_password",
  database="your_database"
)

mycursor = mydb.cursor()

sql = "INSERT INTO your_table (column1, column2, ...) VALUES (%s, %s, ...), (%s, %s, ...), ..."

val = ("John Doe", 35, "New York")
val2 = ("Jane Smith", 28, "Chicago")

mycursor.execute(sql, val)
mycursor.execute(sql, val2)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

1. Establish a connection: Connect to your MySQL database using the appropriate credentials.
2. Prepare the SQL statement: Create an SQL statement using the INSERT INTO syntax, including placeholders for the values.
3. Prepare the statement: Prepare the statement using the database driver's methods (e.g., prepare() in PHP).
4. Bind parameters: Bind the values to the placeholders using methods like bind_param() in PHP or execute() with a tuple of values in Python.
5. Execute the statement: Execute the prepared statement to insert the data into the database.
6. Check for success: Verify if the insertion was successful by checking the affected rows or any error messages.
7. Close the connection: Close the database connection when finished.

Bulk Insert:

• Purpose: For inserting a large number of rows efficiently.
• Methods:
  • LOAD DATA INFILE: Reads data from a text file and inserts it into a table.

    LOAD DATA INFILE 'your_data.csv'
    INTO TABLE your_table
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
    

  • INSERT INTO ... SELECT: Inserts rows from the result set of a SELECT query.

    INSERT INTO your_table (column1, column2)
    SELECT column1, column2
    FROM other_table
    WHERE condition;
    

  • Database-specific bulk insert APIs: Some databases provide native APIs for bulk insertion, which can be more efficient than using standard SQL statements.

Stored Procedures:

• Purpose: Encapsulate complex logic and improve performance.
• Method: Create a stored procedure that performs the bulk insert operation.

  CREATE PROCEDURE insert_multiple_rows()
  BEGIN
    INSERT INTO your_table (column1, column2)
    VALUES ('value1', 1),
           ('value2', 2),
           ...;
  END;
  

  Call the stored procedure:

  CALL insert_multiple_rows();
  

Batching:

• Purpose: Break down a large number of inserts into smaller batches to improve performance and reduce resource consumption.
• Method:
  • Divide the data into smaller batches.
  • Use a loop to insert each batch.
  • Commit or rollback the transaction for each batch.

Asynchronous Inserts:

• Purpose: Improve performance by executing inserts asynchronously.
• Method:
  • Use asynchronous programming techniques (e.g., asynchronous I/O, message queues) to initiate inserts without waiting for completion.
  • Handle asynchronous results or errors appropriately.

Database-Specific Features:

• Purpose: Leverage database-specific features for optimized bulk insertion.
• Examples:
  • Oracle: Parallel DML, SQL*Loader
  • SQL Server: Bulk Copy Operation (BCO)
  • PostgreSQL: COPY

Choosing the Best Method:

The optimal method depends on factors such as:

• Data volume: For very large datasets, bulk insert methods like LOAD DATA INFILE or database-specific APIs might be more efficient.
• Data source: If data is already in a specific format (e.g., CSV), LOAD DATA INFILE can be a good choice.
• Performance requirements: For high-performance scenarios, consider using stored procedures, asynchronous inserts, or database-specific bulk insert features.
• Complexity: If the insertion logic is complex, stored procedures can help encapsulate it.