? MySQL INSERT INTO

The INSERT INTO statement is used to add new rows of data into a MySQL table.

DML Command

⚡ Quick Overview

INSERT INTO is used to add one or more new records into an existing table.
You can specify the columns you want to insert values into.
The VALUES clause holds the actual data for each column.
Multiple rows can be inserted in a single statement for better performance.

? Key Concepts

Target table: The table where new rows will be inserted.
Column list: The columns that will receive the data (e.g., (name, age, dept)).
VALUES: The actual values in the same order as the column list.
Single-row INSERT: Inserts one new row at a time.
Multi-row INSERT: Inserts multiple rows in a single query.
NULL values: Used when you want to leave a column empty (if allowed).

? Syntax & Theory

The general pattern of an INSERT INTO statement is:

? View Syntax Example

-- Basic syntax for inserting a single row
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Syntax for inserting multiple rows at once
INSERT INTO table_name (column1, column2, column3)
VALUES (value1a, value2a, value3a),
       (value1b, value2b, value3b);

The column list is optional only when you provide values for all columns in the exact table order. However, it is strongly recommended to always specify the column names for clarity and safety.

? Code Examples with INSERT INTO

Suppose we have a table named students with columns: id, name, age, department.

? View Code Example – Single & Multiple Inserts

-- Insert a single record into the 'students' table
INSERT INTO students (name, age, department)
VALUES ('John Doe', 22, 'Engineering');

-- Insert multiple records into the 'students' table
INSERT INTO students (name, age, department)
VALUES ('Jane Smith', 23, 'Science'),
       ('Mark Johnson', 20, 'Arts');

? View Code Example – Using NULL & Auto-Increment

-- Assuming 'id' is AUTO_INCREMENT, we can skip it and insert NULL for optional fields
INSERT INTO students (name, age, department)
VALUES ('Alice Brown', 21, NULL);

? What Actually Happens?

? Execution & Result

Each INSERT INTO statement adds one or more new rows to the students table.
If id is an AUTO_INCREMENT column, MySQL automatically generates its value.
When you insert multiple rows in one statement, MySQL processes them together, which is faster than inserting row-by-row.
Using NULL for a column stores an actual NULL value, as long as the column allows it.

INSERT INTO is always used with an existing table structure.
The number of values in each row must exactly match the number of specified columns.
If a value violates a constraint (e.g., wrong data type, NOT NULL column left NULL), MySQL will throw an error.

✅ Tips & Best Practices

Always ensure the data types of the values match the column types in the table.
Use NULL only when you intentionally want an “unknown / empty” value and the column allows it.
When inserting multiple rows, make sure each row has the same number of values as the column list.
Explicitly specify column names in INSERT INTO to avoid issues if the table structure changes later.
Be careful when inserting data into production databases—validate data first to avoid bad records.

? Try It Yourself

Create a students table (if not already created) with columns: id, name, age, department.
Write a query to insert a new student with their name, age, and department.
Insert at least three students in one INSERT INTO statement.
Experiment with inserting NULL into the department column to see how it behaves.
Try inserting invalid data (e.g., a string into an integer column) and observe the error message.