The MySQL INSERT INTO statement adds a new record (row) to a database table. If you want to store data in a MySQL table, you must use the INSERT INTO statement. For example, if you have a students table and want to add a new student’s information, you can use the INSERT INTO command.
The INSERT INTO command is one of the most commonly used SQL commands. This command allows you to add new data such as student records, employee details, customer information, and product data to a database table.
Syntax of MySQL INSERT INTO Statement
There are two ways to write the INSERT INTO statement in MySQL:
Syntax 1: INSERT INTO with Column Names
This syntax allows you to specify the names of the columns into which you want to insert data.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
In this syntax:
- table_name is the name of the table where you want to insert data.
- column1, column2, column3, … are the columns that will receive the values.
- VALUES contains the data to be inserted.
- Each value corresponds to the specified column in the same order.
Example 1:
Suppose you have a students table:
INSERT INTO students (id, name, age, city)
VALUES (101, 'John', 20, 'New York');
The values are inserted into the corresponding columns as shown below:
| Column Name | Value Inserted |
|---|---|
| id | 101 |
| name | John |
| age | 20 |
| city | New York |
Resulting Table
| id | name | age | city |
|---|---|---|---|
| 101 | John | 20 | New York |
How MySQL Maps the Values
| Specified Column | Corresponding Value |
|---|---|
| id | 101 |
| name | ‘John’ |
| age | 20 |
| city | ‘New York’ |
Since the columns and values are listed in the same order, MySQL inserts:
- 101 into the id column.
- ‘John’ into the name column.
- 20 into the age column.
- ‘New York’ into the city column.
As a result, MySQL adds one new row to the students table.
Syntax 2: INSERT INTO Without Column Names
If you provide values for all columns in the table, you can omit the column names.
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
In this syntax:
- MySQL inserts values according to the order of columns defined in the table.
- You must provide a value for every column.
- The order of values must exactly match the order of the columns.
Example 2: INSERT INTO Without Specifying Column Names
INSERT INTO students
VALUES (102, 'David', 22, 'Chicago');
In this example, we have not specified the column names. Therefore, MySQL inserts the values according to the order of columns defined in the students table.
How MySQL Maps the Values
| Table Column | Value Inserted |
|---|---|
| id | 102 |
| name | David |
| age | 22 |
| city | Chicago |
MySQL inserts the values in the following order:
- 102 is inserted into the id column.
- ‘David’ is inserted into the name column.
- 22 is inserted into the age column.
- ‘Chicago’ is inserted into the city column.
Since the values are provided in the same order as the table columns, the record is inserted successfully.
Resulting Table
| id | name | age | city |
|---|---|---|---|
| 102 | David | 22 | Chicago |
Note that when you omit column names, you must provide values for all columns and follow the exact order of columns in the table. However, you should follow syntax 1 because of the following reasons:
- Easy to read and understand.
- Reduces errors when the table structure changes.
- Allows you to insert data into selected columns only.
Example 3: Insert Values into Specific Columns
The INSERT INTO statement in MySQL also allows you to specify only the columns for which you want to insert values. You do not need to insert values into every column of a table.
Any columns that are omitted automatically receive their default value (if one exists) or NULL if the column allows NULL values. This feature is useful when you do not have data for every column or when some columns are optional. Look at the below example query.
INSERT INTO students(id, name)
VALUES(104, 'Michael');How MySQL Maps the Values
| Specified Column | Value Inserted |
|---|---|
| id | 104 |
| name | Michael |
Resulting Table
| id | name | age | city |
|---|---|---|---|
| 104 | Michael | NULL | NULL |
In this example:
- The id column stores the value 104.
- The name column stores the string value ‘Michael’.
- Since we have not provided values for the age and city columns, MySQL stores NULL for both columns.
- Here, NULL means no value or unknown value.
How to Insert Multiple Rows at Once?
MySQL allows you to insert multiple records into a table using a single INSERT INTO statement. Instead of executing several separate queries, you can insert many rows at the same time. The following SQL query inserts three new records in the “students” table:
INSERT INTO students(id, name, age, city)
VALUES
(105, 'Emma', 19, 'Dallas'),
(106, 'Sophia', 20, 'Houston'),
(107, 'James', 21, 'Seattle');In the above query, each set of values is enclosed in parentheses and separated by commas. You must end the statement with a semicolon (;).
How MySQL Maps the Values
First Row:
| Column | Value |
|---|---|
| id | 105 |
| name | Emma |
| age | 19 |
| city | Dallas |
Second Row:
| Column | Value |
|---|---|
| id | 106 |
| name | Sophia |
| age | 20 |
| city | Houston |
Third Row:
| Column | Value |
|---|---|
| id | 107 |
| name | James |
| age | 21 |
| city | Seattle |
Resulting Table
| id | name | age | city |
|---|---|---|---|
| 105 | Emma | 19 | Dallas |
| 106 | Sophia | 20 | Houston |
| 107 | James | 21 | Seattle |
In this example:
- The first set of values (105, ‘Emma’, 19, ‘Dallas’) creates the first row.
- The second set of values (106, ‘Sophia’, 20, ‘Houston’) creates the second row.
- The third set of values (107, ‘James’, 21, ‘Seattle’) creates the third row.
MySQL processes all three sets of values and inserts three records into the students table with a single query. This is the most efficient way to insert multiple records into a MySQL table using a single query.
