How do I insert multiple records into a table in MySQL?

Insert multiple rows in MySQL with the help of “values”. You can enclose the values with parentheses set with comma separation. The syntax is as follows to insert multiple rows in MySQL.

insert into yourTableName(yourColumnName1,yourColumnName2,..............yourColumnNameN) values(value1,value2,...valueN),(value1,value2,...valueN),(value1,value2,...valueN),...........((value1,value2,...valueN);

Let us now first create a table in MySQL −

mysql> create table InsertMultipleRowsDemo
   -> (
   -> Id int,
   -> Name varchar(200),
   -> Age int
   -> );
Query OK, 0 rows affected (0.45 sec)

Apply the above syntax to insert multiple rows. The query is as follows −

mysql> insert into InsertMultipleRowsDemo(Id,Name,Age) values(1,'John',23),(2,'Carol',24),(3,'Johnson',21),(4,'Smith',20),(5,'David',26);
Query OK, 5 rows affected (0.31 sec)
Records: 5 Duplicates: 0 Warnings: 0

Display all records with the help of select statement. The query is as follows −

mysql> select *from InsertMultipleRowsDemo;

The following is the output −

+------+---------+------+
| Id   | Name    | Age  |
+------+---------+------+
| 1    | John    | 23   |
| 2    | Carol   | 24   |
| 3    | Johnson | 21   |
| 4    | Smith   | 20   |
| 5    | David   | 26   |
+------+---------+------+
5 rows in set (0.00 sec)

Many times developers ask that is it possible to insert multiple rows into a single table in a single statement. Currently, developers have to write multiple insert statements when they insert values in a table. It is not only boring but also time-consuming.

Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

Example 1:

To create a table in the database, first, we need to select the database in which we want to create a table.

Then we will write a query to create a table named student in the selected database 'dbs'.

The student table is created successfully.

Now, we will write a single query to insert multiple records in the student table:

To verify that multiple records are inserted in the items_tbl table, we will execute the SELECT query.

Insert Multiple Records Into MySQL Using MySQLi and PDO

Multiple SQL statements must be executed with the mysqli_multi_query() function.

The following examples add three new records to the "MyGuests" table:

Example (MySQLi Object-oriented)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";

if ($conn->multi_query($sql) === TRUE) {
  echo "New records created successfully";
} else {
  echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Note that each SQL statement must be separated by a semicolon.

Example (MySQLi Procedural)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";

if (mysqli_multi_query($conn, $sql)) {
  echo "New records created successfully";
} else {
  echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>

The PDO way is a little bit different:

Example (PDO)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // begin the transaction
  $conn->beginTransaction();
  // our SQL statements
  $conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('John', 'Doe', '[email protected]')");
  $conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('Mary', 'Moe', '[email protected]')");
  $conn->exec("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES ('Julie', 'Dooley', '[email protected]')");

  // commit the transaction
  $conn->commit();
  echo "New records created successfully";
} catch(PDOException $e) {
  // roll back the transaction if something failed
  $conn->rollback();
  echo "Error: " . $e->getMessage();
}

$conn = null;
?>

How do I insert multiple rows in a table?

How to insert 100 rows in SQL?

How to insert 10000 records in MySQL?

Which method is used to insert multiple rows into a MySQL table at a time?