MySQL Insert Multiple Rows

MySQL is an open-source Relational Database Management System that stores data in rows and columns. MySQL is designed to be platform–independent, which means it can run on various operating systems, including Windows, Linux, macOS, and more. MySQL is scalable and can handle databases of varying sizes. It is suitable for small-scale applications as well as large-scale applications.

In this article, we will understand How to Insert Multiple Rows with different approaches and so on. Inserting multiple rows in a table reduces the number of queries which is efficient for the memory.

Here we will use different methods such as INSERT INTO VALUES, INSERT INTO SELECT, LOAD DATA INFIL, and Batch Inserts.

Step 1: Creating a Table

We can create a table by using the “Create Table” Statement.

Syntax:

CREATE TABLE table_name 
(
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (one_or_more_columns)
);

Example 1: Creating a Table Using “CREATE TABLE” Statement

Query:

CREATE TABLE students 
(
   StudentID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50)
);

By using “INSERT INTO” statement we can insert multiple rows into the table in which we can provide multiple values in the VALUES clause and the values will be inserted.

All we have to do is use the INSERT INTO command with our table name and make our desired column, then enter our values according to the columns given.

Syntax:

INSERT INTO your_table_name (column1, column2, column3)
VALUES
  (value1_row1, value2_row1, value3_row1),
  (value1_row2, value2_row2, value3_row2),
  ...
  (value1_rowN, value2_rowN, value3_rowN);

Example 1: Inserting Multiple Rows in MySQL

Query:

Let’s insert some data into the students table.

INSERT INTO students (StudentID, FirstName, LastName)
VALUES
  (1, 'Prakhar', 'Awasthi'),
  (2, 'Manvi', 'Chaturvedi'),
  (3, 'Badal', 'Mishra');

Output:

Explanation: We have inserted records successfully into the students table.

Example 2

Query:

Let’s insert some data into the class table.

INSERT INTO class (StudentID, StudentName, Address)
VALUES
  (21, 'Shagun Kaushal', 'Los Angeles'),
  (22, 'Manvi Chaturvedi', 'Los Angeles'),
  (23, 'Prakhar Awasthi', 'Los Angeles');

Output:

Explanation: We have inserted records successfully into the class table.

We can also use the INSERT INTO SELECT statement to insert multiple values from another table, it simply selects our desired values from a table and inserts them into the table we want.

Syntax:

INSERT INTO destination_table (column1, column2, column3, ...)
SELECT value1, value2, value3, ...
FROM source_table
WHERE conditions;

Example

Query:

In the given, we will be creating a table first and then we will insert the values, and then we will create our destination in which we want to insert the multiple rows with the existing table.

-- Creating first table
CREATE TABLE first_table (
    id INT,
    name VARCHAR(50),
    salary INT,
    department VARCHAR(50)
);

-- Inserting data into first table
INSERT INTO first_table (id, name, salary, department)
VALUES
    (1, 'John Doe', 50000, 'IT'),
    (2, 'Jane Smith', 60000, 'HR'),
    ...

-- Create a second table to insert multiple values using first table
CREATE TABLE second_table (
    id INT,
    name VARCHAR(50),
    salary INT
);

-- Inserting data from first_table into second_table with a condition
INSERT INTO second_table (id, name, salary)
SELECT id, name, salary
FROM first_table
WHERE department = 'IT';

Output for First Table:

Output for Second Table:

Explanation: We have successfully fetched the records from the first_table.

This command is used to insert data from a text file to a table in SQL, by using the “LOAD DATA INFILE” command we can easily insert multiple rows into the table. If there is a condition in which we are having a “data.txt” file and a table of employees with columns id, name, salary, and department as Columns.

Syntax:

LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, salary, department);

Example

Query:

In this example, we will create an employees Table then we will load the file along with their path.

-- Creating Table
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    salary INT,
    department VARCHAR(50)
);
-- Loading the file
LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, salary, department);
-- To show your table
SELECT * FROM employees;

Output:

Explanation: We have inserted records successfully into the employees table.

Using Batch Inserts reduces the no. of query, and we can insert multiple rows in a query. Here the syntax used will be “INSERT INTO” and “VALUES” clause.

Example

Query:

Let’s create a table called classroom and then perform Batch Inserts for Improved Performance operations.

-- Create the table
CREATE TABLE classroom (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    rollno INT,
    department VARCHAR(50)
);


-- Use a single INSERT INTO ... VALUES statement for bulk insert
INSERT INTO classroom (id, name, rollno, department)
VALUES
    (1, 'John Doe', 12, 'IT'),
    (2, 'Jane Smith', 13, 'IT'),
    (3, 'Bob Johnson', 14, 'AI'),
    (4, 'Alice Williams', 15, 'AI'),
    (5, 'Charlie Brown', 16, 'CS'),
    (6, 'Eva Davis', 17, 'IT'),
    (7, 'Frank Miller', 18, 'CS'),
    (8, 'Grace Taylor', 19, 'CS');

Output:

Explanation: We have inserted records successfully into the classroom table.

In this article, we explored how we can insert multiple rows into a table by using the “INSERT INTO” statement and the “VALUES” clause. For inserting multiple rows first, we have to create a table and then we can easily use the INSERT INTO statement to insert rows into the table, you can move to the steps motioned above to insert multiple rows.

How do I insert multiple rows in MySQL?

You can insert multiple rows with a single command by listing them together. For example:

INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4);

Why should I insert multiple rows at once?

Doing this makes your database operations faster and more efficient by reducing the number of separate commands sent to the database. It also keeps your code cleaner and easier to manage.

Is there a limit to how many rows I can insert at once?

Yes, there’s a limit based on the maximum data size the server can handle in one go, usually around 4MB by default. You can change this limit in the server settings if needed.