The INSERT query in MySQL is used to add new rows of data into a table. This is one of the key operations in SQL, enabling you to populate a table with data. Below is an explanation of the INSERT query with its various forms and examples.

1. Basic Syntax

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

• table_name: The name of the table where you want to insert data.
• column1, column2, column3, ...: The columns into which you want to insert data.
• value1, value2, value3, ...: The corresponding values to be inserted into the specified columns.

2. Inserting a Single Row

You can insert a single row of data into a table by specifying values for each column:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'Sales', 50000);

This query inserts a new row into the employees table, with the first name "John," last name "Doe," department "Sales," and salary 50,000.

3. Inserting Multiple Rows

You can insert multiple rows into a table in a single INSERT query by specifying multiple sets of values:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES 
    ('John', 'Doe', 'Sales', 50000),
    ('Jane', 'Smith', 'Marketing', 60000),
    ('Alice', 'Johnson', 'HR', 55000);

This query inserts three rows into the employees table.

4. Inserting Data into Specific Columns

You don't always need to insert data into every column. If a column has a default value or allows NULL, you can omit it from the INSERT statement:

INSERT INTO employees (first_name, last_name)
VALUES ('Tom', 'Anderson');

This query inserts a new row into the employees table with the first name "Tom" and last name "Anderson." The other columns (like department and salary) will be set to their default values or NULL if not specified.

5. Inserting Data Without Specifying Columns

If you provide values for all columns in the table and in the correct order, you can omit the column names:

INSERT INTO employees 
VALUES (NULL, 'Emily', 'Clark', 'IT', 70000);

This assumes that the table has columns in the order: id, first_name, last_name, department, and salary. The NULL is used to auto-increment the id column.

6. Inserting Data from Another Table

You can use an INSERT INTO ... SELECT statement to insert data from one table into another:

INSERT INTO employees_archive (first_name, last_name, department, salary)
SELECT first_name, last_name, department, salary 
FROM employees 
WHERE department = 'Sales';

This query copies all employees from the Sales department into an employees_archive table.

7. Using ON DUPLICATE KEY UPDATE

MySQL allows you to handle duplicate key scenarios with ON DUPLICATE KEY UPDATE. This is useful when you want to update an existing row instead of inserting a new one if a duplicate key is found:

INSERT INTO employees (id, first_name, last_name, department, salary)
VALUES (1, 'John', 'Doe', 'Sales', 55000)
ON DUPLICATE KEY UPDATE salary = 55000;

If a row with id = 1 already exists, this query will update the salary to 55,000 instead of inserting a new row.

8. Handling NULL Values

When inserting data, if you want to insert a NULL value into a column, you can do so explicitly:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Bob', 'Brown', NULL, 45000);

Here, the department field will be set to NULL.

9. Using DEFAULT Keyword

You can also use the DEFAULT keyword to insert the default value for a column:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Alice', 'White', DEFAULT, 40000);

This query will insert the default value for the department column.

10. Error Handling

• Primary Key Violations: If you try to insert a row with a primary key value that already exists, MySQL will throw an error unless you use ON DUPLICATE KEY UPDATE.
• Data Type Mismatch: Ensure that the values you insert match the data types of the columns. For example, trying to insert a string into an integer column will result in an error.

11. Performance Considerations

• Batch Inserts: Inserting multiple rows at once (batch inserts) is more efficient than inserting rows individually.
• Indexes: If you are inserting a large number of rows, disabling indexes temporarily can speed up the process. Remember to re-enable them afterward.