In my previous article, Learn MySQL: Sorting and Filtering data in a table, we had learned about the sorting and filtering of the data using WHERE and ORDER BY clause.

Insert statement is a DML (Data modification language) statement which is used to insert data in the MySQL table. Using the Insert query, we can add one or more rows in the table. Following is the basic syntax of the MySQL INSERT Statement.

In syntax,

1. First, you must specify the name of the table. After that, in parenthesis, you must specify the column name of the table, and columns must be separated by a comma
2. The values that you want to insert must be inside the parenthesis, and it must be followed by the VALUES clause

If you want to insert more than one row at the same time, the syntax is slightly different. In the value keyword, you must specify a comma-separated list of the rows. Here, each element is considered as one row. All the rows must be comma-separated. The following is the syntax:

• Note: Here, make sure that the number of columns should match with the number of values. You can exclude the identity column of a table, or a default constraint is specified on any of the columns of the table. In upcoming articles, I will explain the default constraints and identity columns.

To demonstrate the usage of the INSERT Statement, I have created a table named tblemployees on the VSData database. Below is the definition of the tblemployees table.

Let me show various use cases of the insert query. In this article, I am going to cover the following:

1. Simple INSERT statement to add data to the table
2. Use INSERT Statement to add multiple rows in the table
3. INSERT INTO SELECT clause to insert the output generated by the SELECT query
4. INSERT IGNORE clause to ignore the error generated during the execution of the query

Simple INSERT query example

Suppose I want to insert the first name and last name of the employee. Run the following query:

Once the query executed successfully, let us run the SELECT query to verify the data. Execute the following query:

Following is the screenshot of the output:

As you can see in the above screenshot of the output, the row has been inserted successfully, and the values of their corresponding columns are also inserted correctly. In the INSERT query, I have specified the values of the first_name and last_name column, but we have set the default value of the department_id column, so if we do not specify the value of the column, it inserts the default value. The employee_id column is auto_increment so that it will be incremented automatically.

Insert a date in the table

Suppose you want to insert a specific joining date for an employee. The joining date will be inserted in the employee_joining_date column. The query should be written as follows:

Run the select query to verify the output:

Output:

As you can see, the joining date is inserted correctly. Now instead of specifying a joining date, we want to use the current date. In this scenario, we can use a built-in function named CURRENT_DATE(). This function gets the date of the server. It is like the getdate() function of SQL Server.

The query should be written as follows:

Run the SELECT query:

Below is the output:

As you can see, the joining date of the employee ID is the current date.

Insert multiple rows in the table

We want to insert details of the two employees in the tblemployee table. To do that, execute the following query:

Once rows are inserted, execute the SELECT statement to verify the output:

The following is the output:

• Note: In MySQL, there is a configuration parameter named max_allowed_packat_size to limit the size of the packet. The maximum package size in MySQL 8.0 server and client is 1GB.

If you are inserting multiple rows and the size of the INSERT query is higher than the value configured in max_allowed_packat_size, the query will give the ER_NET_PACKET_TOO_LARGE error and close the connection. You can see the value of the parameter by executing the following command:

Output:

The value of the configuration parameter can be changed by executing the following command.

INSERT INTO SELECT Query

Suppose we want to insert the result-set that is generated by another SELECT query in the table, we can use INSERT INTO SELECT Query. The syntax is the following:

As you can see in the syntax, instead of using the VALUES clause, we have used the SELECT query. Here SELECT statement retrieves the data from another table or by joining different tables. This query is very helpful when you want to create a backup of a specific table.

To demonstrate the scenario, I am going to use the actor table of the sakila database. I have created another table named actor_backup. The table structure of the table actor_backup and actor is the same. The only change I made is that I have dropped the foreign keys and other constraints of the actor_backup table.

The table can be created by executing the following query.

Now, to insert data from the actor table to the actor_backup table, execute the following query.

Execute the following SELECT query to verify that data has been inserted successfully.

Output:

INSERT IGNORE statement

INSERT IGNORE is a very interesting keyword. When we insert multiple records in the using INSERT statement or INSERT INTO SELECT keyword, and sometimes the INSERT query failed due to an error, the entire INSERT query gets terminated. Suppose we are inserting thousands of records in a table, and due to error, the entire INSERT query fails and we must re-run the entire query. To avoid such issues, we can use the INSERT IGNORE statement.

If you are using the INSERT IGNORE statement to insert data in tables, even though the query encounters an error, the INSERT query does not fail.

To demonstrate, I have created a table named tbldepartment. The following is the table definition:

Here, Department_ID column is a primary key so you cannot insert a duplicate value in it. Now, let’s insert a few rows by executing the following query:

When you insert the record, you will receive the following warning.

Here, we have used the INSERT IGNORE statement; hence two rows must be inserted in the table. To verify that, execute the following SELECT query.

Below is the output:

As you can see, instead of three values, only the DEP00001 and DEP00002 have been inserted.

When we use INSERT INTO IGNORE keyword, the MySQL will issue the warning, but it will try to adjust the value in the column. To understand that, insert another row in the tbldepartment table. The length of the department_id column is 10 characters. In the table, let us try to insert the department_id. The length of the value is higher than the defined length. Execute the following query:

The query will give the following warning:

As you can see, the query generates a warning. Now, run the SELECT query to view the data.

Below is the output:

As you can see, that the row has been inserted, but MySQL has trimmed the value of the department_id column.

Summary

In this article, we have learned how we can insert one or more than one row in the MySQL table using the INSERT statement. We also covered how we can insert the result set, generated by another query in tables. Additionally, we learned how to ignore the error generated by the INSERT statement. In the next article, we are going to learn about MySQL UPDATE and DELETE statements and their use cases. Stay tuned..

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022