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.

Creating a Table and Inserting Multiple Rows

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)
);

Method 1: Using INSERT INTO VALUES

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 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:

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:

Output

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

Method 2: Using INSERT INTO SELECT

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

Syntax:

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

Example 1:

Query:

In the given we will be creating table first and then we will insert the values it, and then we will create our destination in which we want to insert the multiples 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:

First Table

Output for Second Table:

Second Table

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

Method 3: Using LOAD DATA INFILE

This command is used to insert data from a text file to a table in SQL, by using “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 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 1:

Query:

In this example, we will create an employees Table then we will load the file along with the 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:

Using LOAD DATA INFILE

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

Method 4: Using Batch Inserts for Improved Performance

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

Example 1:

Query:

Let’s create an table called classroom then performed 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:

Using Batch Inserts

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

Conclusion

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