MySQL INSERT Statement

The `INSERT` statement in MySQL is used to add new rows of data into a table. It is essential for populating tables with initial data or appending new records.

Usage

The `INSERT` statement is employed when you need to add new data to a table. It can insert single or multiple rows at once, specifying the values for each column.

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

In this syntax, `INSERT INTO` defines the target table and columns, while `VALUES` specifies the data to be inserted. Additionally, the `INSERT ... SET` syntax can be used to specify column-value pairs directly:

INSERT INTO table_name SET column1 = value1, column2 = value2;

Examples

1. Basic Insert

INSERT INTO products (product_name, price)
VALUES ('Laptop', 999.99);

This example inserts a single row into the `products` table with values for `product_name` and `price`.

2. Inserting Multiple Rows

INSERT INTO employees (first_name, last_name, position)
VALUES 
  ('John', 'Doe', 'Manager'),
  ('Jane', 'Smith', 'Developer');

This syntax demonstrates inserting multiple rows into the `employees` table in one statement for efficiency.

3. Insert Using SELECT

INSERT INTO archived_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date < '2022-01-01';

This example copies data from the `orders` table to `archived_orders` for records with an order date before 2022, useful for tasks such as data archiving or migration.

4. Insert with Ignore and Duplicate Key Update

INSERT IGNORE INTO users (user_id, username)
VALUES (1, 'johndoe');

This statement attempts to insert a row and ignores it if a duplicate key is encountered.

INSERT INTO users (user_id, username)
VALUES (1, 'johndoe')
ON DUPLICATE KEY UPDATE username = 'john_updated';

This example updates the `username` if a duplicate key exists.

Tips and Best Practices

• Specify column names. Always specify column names to ensure data is inserted into the correct columns, maintaining clarity and avoiding errors.
• Use transactions. For bulk inserts or critical data, use transactions to ensure data integrity.
• Check for duplicates. Use `INSERT IGNORE` or `ON DUPLICATE KEY UPDATE` to handle potential duplicate entries gracefully.
• Validate data types. Ensure the values being inserted match the data types defined in the table schema.
• Proper indexing. Consider proper indexing to enhance performance, especially when frequently inserting large amounts of data.
• Handle NULL values. Be mindful of NULL values and their implications during insert operations, ensuring that columns with NOT NULL constraints are appropriately handled.