MySQL INSERT Statement

Introduction

In this article, we will go to elaborate the concept of MySQL INSERT statement with its various methods.

What do you mean by MYSQL Insert statement?

MySQL INSERT statement is used to store or add data in MySQL table within the database. We can perform insertion of records in two ways using a single query in MySQL:

1. Insert record in a single row
2. Insert record in multiple rows
3. Insert with Select statement
4. Insert with Ignore statement
5. Insert with Set statement

Syntax:

The below is generic syntax of SQL INSERT INTO command to insert a single record in MySQL table:

In the above syntax, we first have to specify the table name and list of comma-separated columns. Second, we provide the list of values corresponding to columns name after the VALUES clause.

NOTE: Field name is optional. If we want to specify partial values, the field name is mandatory. It also ensures that the column name and values should be the same. Also, the position of columns and corresponding values must be the same.

If we want to insert multiple records within a single command, use the following statement:

In the above syntax, all rows should be separated by commas in the value fields.

MySQL INSERT Example

Let us understand how INSERT statements work in MySQL with the help of multiple examples. First, create a table "People" in the database using the following command:

1. If we want to store single records for all fields, use the syntax as follows:

2. If we want to store multiple records, use the following statements where we can either specify all field names or don't specify any field.

3. If we want to store records without giving all fields, we use the following partial field statements. In such case, it is mandatory to specify field names.

In the below output, we can see that all INSERT statements have successfully executed and stored the value in a table correctly.

We can use the below syntax to show the records of the People table:

We will get the output as follows:

Inserting Date in MySQL Table:

We can also use the INSERT STATEMENT to add the date in MySQL table. MySQL provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME, and YEAR. The default format of the date in MySQL is YYYY-MM-DD.

This format has the below descriptions:

• YYYY: It represents the four-digit year, like 2020.
• MM: It represents the two-digit month, like 01, 02, 03, and 12.
• DD: It represents the two-digit day, like 01, 02, 03, and 31.

Following is the basic syntax to insert date in MySQL table:

If we want to insert a date in the mm/dd/yyyy format, it is required to use the below statement:

3. Insert with Select statement

Using the SELECT statement, you can also insert values selected from one table into another table.

The syntax is:

Explanation: In the above syntax, table_name represents the table name in which we are inserting data. Column_name1, column_name2 represent the columns in which the values will be inserted. Sometimes column_name is optional but if specified, the number of columns referenced in the INSERT statement must be equal to the number of columns in the SELECT statement. WHERE condition is used to add a constraint to the INSERT statement.

Take an example of two tables such as PEOPLE and PEOPLE_INFO. In PEOPLE_INFO table we want to insert to information such as name of people, occupation and age from the PEOPLE table which is already created.  Before inserting the data it make sure that the PEOPLE_INFO table having the same columns data type's constraints as that of PEOPLE table.

To insert the rows present in the PEOPLE_INFO table into the PEOPLE table is as follows.

Explanation: This statement will insert the name of people, occupation and age records of the PEOPLE table into the PEOPLE_INFO table.

If you want to check the contents of the PEOPLE_INFO table then, use the following SELECT statement.

Output: Following is the output of this example.

4. INSERT IGNORE Statement

If you want to insert data into a table ignoring any duplicate entries, this can be accomplished using the INSERT IGNORE statement.

The syntax is:

Explanation: In the above syntax,

• table_name represents the table name.
• The IGNORE keyword is used to ignore the duplicate entry in the table and the statement continues to exceute without any error.
• Column names in which the values will be inserted in a table.
• WHERE condition is used to add a constraint to the INSERT statement.

Take an example of PEOPLE table with a unique column called id. If we want to insert a new record, but if a record with the same ID already exists, we want to ignore the insert.

To insert the row into the PEOPLE table is as follows.

Explanation: This statement does not insert any record into a table because 106 id already exist.

If you want to check the contents of the PEOPLE table then, use the following SELECT statement.

Output: After running this query, the table will look the same as above because the ignore statement ignores the duplicate content and the statement runs without any errors.

5. Insert with Set statement

If you want to insert data into a table by setting values in selected columns, this can be accomplished using the INSERT VALUE WITH SET statement.

The syntax is:

Explanation: In the above syntax,

• table_name represents the name of table.
• SET keyword is used to set the values of columns in a table and the statement continues to run without any error.
• Column names stores the values will be inserted in a table.

Take an example of PEOPLE table to insert data.

To insert the row into the PEOPLE table is as follows.

Explanation: Above statement insert the specified value into the table.

If you want to check the contents of the PEOPLE table then, use the following SELECT statement.

Output: After running this query, following is the output of table.

Frequently asked questions on MySQL INSERT statement

1. List of some key points while inserting data in a MySQL Insert statement?

Answer: Following is the some key points while inserting data in a MySQL insert statement.

• Check for duplicate values: Use the 'INSERT IGNORE' statement to handle the possible duplicate entries in a table.
• Validate data types: Ensure that the values being inserted into the table match the data types defined in the table structure.
• Indexing: Indexing helps to increase the performance, especially when large amounts of data are inserted frequently.