MySQL Insert

MySQL INSERT Statement

The INSERT statement in MySQL is used to add new rows of data into a table. It allows you to insert one or more rows at a time.

Syntax

Basic Syntax

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

table_name: The table where data will be inserted.
column1, column2, ...: The columns where the values will be inserted.
value1, value2, ...: The actual values to insert into the specified columns.

Inserting Data into All Columns

If you want to insert values into all columns of the table, you can omit the column names:

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

Examples

1. Insert a Single Row

Suppose you have a table called employees with columns: id, name, and position.

INSERT INTO employees (id, name, position)
VALUES (1, 'John Doe', 'Manager');

2. Insert Data into All Columns

If all columns are specified in the same order as the table structure:

INSERT INTO employees
VALUES (2, 'Jane Smith', 'Developer');

3. Insert Multiple Rows

You can insert multiple rows with a single INSERT statement:

INSERT INTO employees (id, name, position)
VALUES 
(3, 'Alice Johnson', 'Designer'),
(4, 'Bob Brown', 'Tester');

4. Insert Data with Default Values

You can insert data while letting MySQL use default values for certain columns.

Assume the id column is set to auto-increment:

INSERT INTO employees (name, position)
VALUES ('Charlie White', 'Analyst');

5. Insert Data from Another Table

You can use a SELECT statement to insert data into a table from another table.

INSERT INTO employees_archive (id, name, position)
SELECT id, name, position
FROM employees
WHERE position = 'Manager';

6. Insert with IGNORE to Avoid Errors

The INSERT IGNORE statement prevents errors if duplicate entries are encountered (e.g., duplicate primary keys).

INSERT IGNORE INTO employees (id, name, position)
VALUES (1, 'John Doe', 'Manager');

7. Insert with ON DUPLICATE KEY UPDATE

If a row with the same primary key already exists, you can update it instead of inserting a new row.

INSERT INTO employees (id, name, position)
VALUES (1, 'John Doe', 'Manager')
ON DUPLICATE KEY UPDATE position = 'Senior Manager';

Best Practices

Validate Data: Ensure that the values match the data type and constraints of the columns.
Use Prepared Statements: When inserting data programmatically, use prepared statements to prevent SQL injection.
Auto-Increment Columns: Let MySQL handle auto-incremented columns rather than manually assigning values.
Batch Inserts: Use multiple-row inserts for better performance when inserting large datasets.

Common Errors

Column Count Mismatch: The number of columns in the INSERT statement must match the number of values.
```
INSERT INTO employees (id, name) VALUES (1, 'John Doe', 'Manager'); -- Error
```
Constraint Violations: Attempting to insert data that violates constraints (e.g., unique, foreign key).
Data Type Mismatch: Trying to insert a value of the wrong type (e.g., string into an integer column).

Conclusion

The INSERT statement is a fundamental operation in MySQL for adding new records. With its various options, you can efficiently add data to your tables while ensuring data integrity.

Souy Soeng