MySQL - Insert Query

Mastering the MySQL INSERT Statement

Once a table is created, it’s an empty blueprint. To bring it to life, you need to populate it with data. The INSERT statement is the fundamental command in SQL for adding new rows to a table. It’s the ‘C’ (Create) in the essential CRUD (Create, Read, Update, Delete) operations that form the backbone of most database-driven applications.

Core INSERT Syntax

The most common and robust way to insert data is by specifying both the column names and the values you want to insert. This practice ensures your query remains stable even if the table structure changes (e.g., columns are added or reordered).

Syntax

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

String and date values must be enclosed in single quotes (e.g., ‘Hello World’). Numeric values do not require quotes.

Example: A Modern products Table

Let’s create a more realistic products table using modern data types and conventions.

CREATE TABLE `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(100) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  `stock_quantity` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Now, let’s insert a single product into our new table:

INSERT INTO `products` (sku, name, price, stock_quantity)
VALUES ('WM-101-BLK', 'Ergonomic Wireless Mouse', 29.99, 150);

Advanced INSERT Techniques

Inserting Multiple Rows

To insert multiple rows efficiently, you can list multiple value tuples in a single INSERT statement. This is significantly faster than running individual INSERT statements for each row as it reduces network overhead and database load.

INSERT INTO `products` (sku, name, price, stock_quantity)
VALUES
  ('KB-MECH-01', 'Mechanical Keyboard', 119.95, 75),
  ('MON-4K-27', '27-inch 4K Monitor', 349.50, 40),
  ('HUB-USBC-8', '8-in-1 USB-C Hub', 45.00, 210);

Best Practice: Always specify the column names in your INSERT statements. The syntax INSERT INTO table VALUES (...) without column names is brittle and will break if the table’s column order changes.

Inserting Data from Another Table (INSERT ... SELECT)

This powerful technique allows you to copy and transform data from one table to another. It’s perfect for archiving old records, creating summary tables, or migrating data.

Example

Let’s create a table for discounted products and populate it with items from our products table that are under $50.

CREATE TABLE `discounted_products` LIKE `products`;

INSERT INTO `discounted_products` (id, sku, name, price, stock_quantity, created_at)
SELECT id, sku, name, price, stock_quantity, created_at
FROM `products`
WHERE price < 50.00;

The INSERT ... TABLE syntax mentioned in older documentation is non-standard and has been deprecated or removed in modern MySQL versions. Always use the standard INSERT ... SELECT for this purpose.

Upserting Data with ON DUPLICATE KEY UPDATE

A common requirement is to insert a new row, but if a row with the same unique key already exists, update it instead. This is called an “upsert” operation. MySQL provides a clean, atomic way to do this.

Example

Let’s try to insert a product with an existing sku. Instead of failing, it will update the price and add to the stock quantity.

INSERT INTO `products` (sku, name, price, stock_quantity)
VALUES ('WM-101-BLK', 'Ergonomic Wireless Mouse v2', 32.50, 50)
ON DUPLICATE KEY UPDATE
  price = VALUES(price),
  stock_quantity = stock_quantity + VALUES(stock_quantity);

The VALUES(column_name) function refers to the value that would have been inserted from the VALUES clause.

Inserting Data via Client Programs: A Secure Approach

When inserting data from an application (like Node.js, Python, or PHP), it’s crucial to prevent SQL injection vulnerabilities. The best practice is to use prepared statements (or parameterized queries), where you send the SQL template and the data separately.

Example: Modern Client Implementations

Node.js (TypeScript)
PHP (PDO)
Java (JDBC)
Python



// Using mysql2/promise for modern async/await syntax and performance.
import mysql from 'mysql2/promise';

async function addProduct(product) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost', user: 'root', password: 'password', database: 'your_db_name'
    });
    const sql = 'INSERT INTO products (sku, name, price, stock_quantity) VALUES (?, ?, ?, ?)';
    const [result] = await connection.execute(sql, [product.sku, product.name, product.price, product.stock]);
    console.log('Product inserted with ID:', result.insertId);
  } catch (error) {
    console.error('Error inserting product:', error);
  } finally {
    if (connection) await connection.end();
  }
}

addProduct({ sku: 'TS-TEE-LG', name: 'TypeScript T-Shirt', price: 24.99, stock: 100 });



// Using PDO is the modern, recommended standard in PHP for its consistency and security features.
$host = 'localhost';
$db   = 'your_db_name';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$sql = "INSERT INTO products (sku, name, price, stock_quantity) VALUES (?, ?, ?, ?)";
$stmt= $pdo->prepare($sql);
$stmt->execute(['PHP-MUG-1', 'PHP Elephant Mug', 15.00, 50]);
$id = $pdo->lastInsertId();
echo "New record created successfully. Last inserted ID is: " . $id;



// Modern Java using try-with-resources for automatic resource management and PreparedStatement.
import java.sql.*;

public class InsertProduct {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db_name";
        String user = "root";
        String password = "password";
        String sql = "INSERT INTO products(sku, name, price, stock_quantity) VALUES(?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, "J-BEAN-CAN");
            pstmt.setString(2, "Java Beans Coffee");
            pstmt.setDouble(3, 19.99);
            pstmt.setInt(4, 80);

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        System.out.println("Product inserted with ID: " + generatedKeys.getLong(1));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



# Using mysql-connector-python with context managers for safe connection handling.
import mysql.connector
from mysql.connector import errorcode

def insert_product(product_data):
    try:
        # Use a dictionary for connection arguments for clarity
        conn_config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'your_db_name'}
        with mysql.connector.connect(**conn_config) as conn:
            with conn.cursor() as cursor:
                sql = "INSERT INTO products (sku, name, price, stock_quantity) VALUES (%s, %s, %s, %s)"
                cursor.execute(sql, product_data)
                conn.commit() # Important: commit changes to the database
                print(f"Product inserted with ID: {cursor.lastrowid}")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)

# Data tuple for insertion
new_product = ('PY-SNAKE-PLUSH', 'Python Plush Toy', 25.50, 120)
insert_product(new_product)

Common Errors and Debugging

• Constraint Violation: Attempting to insert a NULL into a NOT NULL column, or a duplicate value into a UNIQUE or PRIMARY KEY column. The database will reject the insert and return an error.
• Data Type Mismatch: Inserting a string like ‘abc’ into an INT column. MySQL might try to convert it (often to 0), leading to unexpected data, or it might fail completely depending on the server’s SQL mode.
• Column Count Mismatch: The number of columns in the column list does not match the number of values in the VALUES list. The query will fail.
• Debugging Tip: If a multi-row insert statement fails or has warnings, you can run SHOW WARNINGS; immediately after to get more detailed information about what went wrong with each specific row.