INSERT INTO in MySQL

The INSERT INTO statement in MySQL is used to add new records into a table.

1. Basic Syntax

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

✅ You must specify values for all required columns (except AUTO_INCREMENT fields).

2. Example: Insert a Single Row

employees Table

Query: Add a new employee

INSERT INTO employees (name, department, salary)VALUES ('Charlie', 'Sales', 50000);

✅ New Record Added:

3. Insert Multiple Rows

INSERT INTO employees (name, department, salary)VALUES     ('David', 'IT', 70000),    ('Emma', 'HR', 62000);

✅ Inserts multiple records in one query.

4. Insert Without Specifying Columns

INSERT INTO employees VALUES (4, 'Frank', 'Finance', 58000);

✅ Works only if values are provided in the same order as table columns.
⚠️ Not recommended – It can break if the table structure changes.

5. Insert with AUTO_INCREMENT

If a table has an AUTO_INCREMENT primary key, you can skip that column.

INSERT INTO employees (name, department, salary)VALUES ('Grace', 'Marketing', 62000);

✅ MySQL automatically assigns the next available id.

6. Insert Data from Another Table

You can insert data from one table into another using INSERT INTO ... SELECT.

INSERT INTO employees_backup (id, name, department, salary)SELECT id, name, department, salary FROM employees;

✅ Copies data from employees to employees_backup.

7. Using ON DUPLICATE KEY UPDATE

To update a record if a duplicate primary key or unique key exists:

INSERT INTO employees (id, name, department, salary)VALUES (3, 'Charlie', 'Sales', 52000)ON DUPLICATE KEY UPDATE salary = 52000;

✅ If id = 3 exists, it updates the salary.

8. Using IGNORE to Avoid Errors

If a duplicate key exists and you don't want an error, use IGNORE:

INSERT IGNORE INTO employees (id, name, department, salary)VALUES (3, 'Charlie', 'Sales', 52000);

✅ If id = 3 already exists, MySQL skips the insert instead of throwing an error.

9. Insert with Default Values

If a column has a DEFAULT value, you can use:

INSERT INTO employees (name, department)VALUES ('Harry', 'HR');

✅ salary will get its default value if defined.

10. Performance Tips

⚡ Use Bulk Inserts (INSERT INTO ... VALUES (...), (...)) for better performance.
⚡ Use Indexing for faster insert operations.
⚡ Avoid Unnecessary Columns – Insert only required data.

11. Key Takeaways

✅ INSERT INTO adds new records into a table.
✅ Use AUTO_INCREMENT to let MySQL generate IDs.
✅ Use ON DUPLICATE KEY UPDATE to handle conflicts.
✅ Use INSERT INTO ... SELECT to copy data between tables.