MySQL Insert Multiple Rows

How to Insert Multiple Rows in MySQL?

While working with MySQL, sometimes we need to insert one or more than one row in the table of the database. This can be done using the INSERT command. Multiple rows can be added to the table with multiple pieces of information using a single query. In this article, we will see the various methods of adding single and multiple rows in the table of a database. Before that let us have a brief overview of why there is a need for SQL INSERT INTO Multiple rows query.

• The normal SQL INSERT query can insert the data into a single row only. When we use this simple SQL INSERT query to add data to multiple rows, the query will fail. So, we need to use this SQL INSERT differently so that a single query can be used to insert multiple data in multiple rows of the table.
• When we use multiple SQL INSERT queries to add multiple data in multiple rows, this increases the execution time of the query. Using one SQL INSERT query to inject multiple data into multiple rows, helps to achieve a better execution time for the query.

Let us see the various ways of inserting multiple rows in a table with various use cases.

MySQL INSERT Multiple Rows Statement

Let us see how we can use the INSERT statement to insert multiple rows in the table of a database. First, create a table, and then we will use the INSERT statement to add the rows to the table.

To create a table we will use the CREATE command as shown below:

Then we will use the INSERT INTO command to insert the data as shown below:

Explanation

First, we have created a table using the CREATE command that will be used to add rows to them. Then we used the INSERT INTO keyword that instructed the system that we want to add some rows into the created table. Just after that keyword, we use the table name. After that, we use the VALUES clause and write the various values that need to be added as rows in the table separated by commas. Here, the number of rows in the VALUE must be the same as the number of Columns.

MySQL INSERT Multiple Rows Limit

There are some limitations in MySQL servers for inserting the number of rows in a table. Theoretically, we can add as many rows as we want to the table. But, practically, it is not possible to insert an unlimited number of rows. There is a parameter namely max_allowed_packet in MySQL server which sets the limit of rows that can be inserted in a table. If the size of the INSERT statement is larger than the max_allowed_packet, an error namely packet too large occurs and the connection of the MySQL server gets terminated.

We can use the given command to view the current value of the variable max_allowed_packet:

Output:

The number that we get as a value represents the number of bytes.

We can also limit the value for max_allowed_packet using the command given below. The variable size is an integer value that represents the maximum number of bytes that can be allowed in the packet.

The max_allowed_packet does not affect the INSERT INTO ...SELECT statement. When using with SELECT statement, the user can add the number of rows according to their choice.

Examples

Now, let us see the various examples of MySQL inserting multiple rows with different use cases. First, let us create a new table namely Activity, and its schema is defined as shown below:

This will create a table namely Activity and we will get a message "Query OK, 0 rows affected (2.34 sec)" which means the table is successfully created.

MySQL INSERT – Simple INSERT Example

In this example, we will simply add a new row to the existing table using the query as shown below:

After adding the row, we can confirm it by using the query given below. This query will show all the data from the table namely "Activity".

Output:

Explanation:

In this example, we added a row in which there is only the value of Priority and Name is mentioned. And the rest are left as Null values as you can see in the output. The column Work_id is set to AUTO_INCREMENT, the values in this column will automatically keep increasing as the number of rows will increase like 1, 2, and so on. And the columns Start_date, End_date, and Information are left to default value which is NULL in MySQL.

​​

MySQL INSERT – Inserting Rows Using Default Value Example

There are two ways of inserting a default value into the column of a table.

1. Just leave the column name and its value when using the INSERT statement.
2. While writing a query for INSERT INTO statement, specify the column name and then use the keyword DEFAULT in the VALUES clause.

Let us see the second method using an example here.

This will add a row with the default value in the Priority column and the priority is set to value '3' while defining the table. Then we will again use the given query to see the changes in our table and we will get the table as shown below:

MySQL INSERT – Inserting Dates Into the Table Example

Now, let us take an example to understand how we can add a formatted date to the rows of a table. The format to enter a date in the MySQL table is as follows:

The above syntax shows the format of how a date should be inserted into the row. Defining the syntax, the first YYYY represents the four-digit year such as 2023, 2020, etc. Then there is MM which represents the numeric value for the respective month such as 01 for January, 03 for March, and so on. And DD represents the date of that particular month.

To insert a new row with a date in the column namely Start_date and End_date in the table namely Activity, we use the query as shown below:

After running the above query, a new row will be inserted into the table with the specified date. We can again use the " select * from Activity; " to show the change in our table. The output will be like this:

We can also use the function CURRENT_DATE() to insert the current date according to the system in which we are using MySQL. The syntax for using the current_date() function is as follows:

After that, we will get the table as shown below:

MySQL INSERT – Inserting Multiple Rows Example

We can also use the INSERT INTO statement to insert more than one row in the existing table. In this example, we will insert four rows into the table Activity. The query for this will be as shown below:

This will add all four rows provided in the VALUES clause and we will get the updated table as shown below:

We will get a message in our MySQL prompt showing "4 row(s) affected Records: 4 Duplicates: 0 Warnings: 0". It means that 4 rows are added and there are no duplicate rows in the table.

Insert Multiple Rows from the SELECT

We can use the INSERT INTO statement with SELECT to copy multiple rows from one table to another table. One thing to keep in mind is that the column name that we are copying into another table, must have the same name and the same order. Otherwise, it will throw an error and the transaction will not take place. The syntax to use INSERT INTO with SELECT is as follows:

Let's take an example where we will copy some columns of table student_1 to student_2. For that, we need to create two tables namely student_1 and student_2. We will use the below query to create the tables and the structure of the table will be as follows:

Then we can see the new table using the statement " select * from student2 ", and the output will be like this:

Insert Multiple Rows Without Duplicate

We can also insert multiple rows in a table ignoring all the duplicate records. To ignore any duplicate records, the keyword IGNORE is used just after the INSERT in the query statement. The IGNORE keyword works only if there is a primary key. Let's take an example and understand the IGNORE keyword.

The output will be like this: