How To Insert Values in MySQL

In MySQL, the user can add values in the database table by using the INSERT statement. It is a Data Manipulation Language command as it enables the user to make changes in the data. The user can implement the INSERT query to add single or multiple rows of data in the table.

The syntax for INSERT Query

Given below is the syntax to implement the INSERT statement in MySQL:

Explanation of the Syntax:

The user needs to begin by using the INSERT INTO keywords. Then the user has to mention the name of the table in which the user wants to add values in the table. After that, use parenthesis to mention the list of columns for which the user wants to add the data values. Commas separate the columns in the list.

In the second part, use the VALUES keyword to specify the values in the table. The user can mention the data that is to be inserted in parentheses. The values should be respective to the column mentioned in the above step.

Suppose the user wants to add multiple data values in the table using a single statement. The syntax to insert multiple values is slightly different, as in the particular VALUES clause, the user must specify a comma-separated list of the rows to be added to the database table. When inserting multiple rows, every element represents a different row in the table. Both the column values and rows are separated by commas. Values enclosed in parentheses represent a single row.

The syntax to add multiple rows of data is as follows:

Note: The user needs to ensure that the number of columns is exactly the same as the number of values inserted in a row. The user does not require to include the table's identity column or any column with a default constraint in it.

Implementing the INSERT command

Before using the INSERT command in MySQL, the user is required to create a table in the database. The table can be created by using Data Definition Language commands. Below is the query to create a student_table in the database. The query contains the definition of the student_table.

Using the above-defined table, we will discuss the scenarios where the user can implement an INSERT statement to add data values in MySQL. These scenarios are:

• Use the INSERT query to insert the values in the table.
• We are using the INSERT query to add multiple rows of values of data in the table.
• Implement the INSERT command with the SELECT clause in the query. It enables the user to add values in the output the SELECT query returns.
• Using the INSERT IGNORE clause to ignore the error that occurs resulted during the implementation of the statement.

Adding Single Row of Values in INSERT Query

Consider if the user wants to add the fname and lname of the student to the table. Then the user can implement this simple query in MySQL:

Once the user has implemented the above query, the data values will be added to the table. If the user wants to see the changes, then the user must implement a SELECT statement to verify the modifications in the table.

Output:

Once the SELECT query is implemented, the user can check if the row of data values has been added to the table successfully. You can also check that the values are inserted into the corresponding columns. It means the values are added in the correct sequence of columns. In the above INSERT statement, I have mentioned the values for the fname and lname columns.

While defining the table, we declared a default value for the Dept_id column. If the user does not explicitly assign a different value for a particular column, MySQL will automatically assign its default value. Similarly, for the Student_id column, we have used the auto_increment. It automatically assigns one more than the previous value to the next record. MySQL will assign both Student_id and Dept_id.

Add Date as Value Using INSERT Statement

Suppose the user wants to assign a particular date for a student. In this scenario, the date of joining will be added to the Doj column in the student_table. The query to add the date is as follows:

Once this query is successfully implemented, you can implement the below query.

Adding Multiple Rows Using Single INSERT Statement

Suppose the user wants to add details of several students in the table. Then the user can implement the below SQL query to add the data in the student_table.

Once the above query is successfully executed, you can use the SELECT statement to see the updated table.

Output:

Note: In MySQL, a configuration parameter is responsible for limiting the packet size. The maximum packet size is 1GB. If the user tries to enter the data exceeding the maximum packet size, the query will return an error indicating the net packet is too large. It will immediately close the connection. If you want to check the parameter, then you can implement the given command:

Output:

You can change the maximum allowed packet size by implementing the SQL command below.

INSERT INTO SELECT Query

Suppose the user wants to add the result set produced by a different SELECT statement in the table. The user can implement the INSERT INTO SELECT query. The syntax is as follows:

As it is clear from the syntax, the user can use the SELECT query instead of implementing the VALUES clause. The SELECT query is responsible for gathering the data from a different table or merging the different tables. This query is mainly used when the user needs to create a backup for a specific table.

Let us consider an example to implement the INSERT INTO SELECT query. In this case, we will use a stud_table. Now we will create another table for creating the backup for stud_table. Both the stud_table and stud_backup should have precisely the same structure. The user can remove the foreign keys and other restrictions on the stud_backup in the backup table.

The user can implement the below query to define the stud_backup.

Now, the user must add the data from the stud_table to the stud_backup table. The user can implement the below query to create a copy of data in the stud_backup.

The user can ensure that the backup table is successfully created by implementing the SELECT query to display the data in stud_backup.

There are various instances where the user wants to insert multiple data values in the table using an INSERT statement or INSERT INTO SELECT query. There is a high possibility that the INSERT query may fail due to an error. It may cause the entire INSERT query to fail.

Consider a scenario where the user inserts thousands of data values in the table, and the error occurs while inserting the record. Then the complete INSERT query will fail, and the user will have to start from the beginning again to re-implement the complete query. If the user wants to avoid such issues, the user can implement the INSERT IGNORE statement.

Suppose the user implements the INSERT IGNORE query to add multiple data values in the table, even though an error occurs while implementing the query. It will prevent the complete INSERT statement from failing.

Let us use an example where the user can use INSERT IGNORE. In this case, create an employeetbl. Implement the mentioned below query to define the employee.

Here emp_id is assigned as the primary key. It constrains the emp_id column, as the user cannot insert a duplicate record in the table. Implement the query below to add the data in the table using INSERT IGNORE:

When you implement the above query, it will return a warning.

So, in this particular case, we have used the INSERT IGNORE statement; therefore, the entire INSERT statement does not fail. It will be implemented twice, ensuring the other two records will be added to the table. To check the data value entered, implement the given SELECT query:

Output:

Now you can see that only the two values will be added to the table instead of three.

When the user implements the INSERT INTO IGNORE statement, the execution won't fail; instead, MySQL issues a warning. It adds the data values in the respective table column. In order to get a better understanding of the concept, add another row in the employees. The length of the emp_name is set at ten characters. Let us try to add a value in the emp_name that exceeds the maximum size of the value that can be added in the column. Implement the query provided below using INSERT IGNORE INTO.

MySQL will again return a warning, but it will add the values in the table. Implement the below SQL query to display the inserted record.

It will return the following output:

You can see that the row is added to the table, but MySQL reduces the value of the emp_name column.

Summary

In this tutorial, we have discussed how the user can add one or more than one rows in the MySQL table by implementing the INSERT statement. We have also discussed how the user can add values in the result set produced by another statement in tables. Moreover, we have also discussed how to use the INSERT IGNORE statement to ignore the error generated while implementing the INSERT query.