Insert multiple rows at a time in MySQL

In this post, i will show you how to insert multiple rows at a time in MySQL. We will use single INSERT statement to insert multiple rows with multiple data into table. Normally, if we want to create table having 10 rows, we can use 10 INSERT statement to insert rows into table. In this post, we will learn how to use a single MySQL INSERT statement to insert multiple rows into a table at a time. We can insert any number of rows into table using single INSERT statement. How ? Lets see

How to Insert multiple rows in MySQL

Let us say ,you have following table student

You already know that a table is created using CREATE TABLE command. Let us create student table using CREATE TABLE command and then insert above data into student table using single INSERT statement

MySQL CREATE TABLE statement

mysql> CREATE TABLE STUDENT
    -> (
    -> STUDENT_ID INT NOT NULL,
    -> LAST_NAME VARCHAR(30) NOT NULL,
    -> FIRST_NAME VARCHAR(30) NOT NULL,
    -> SCORE INT NOT NULL	
    -> );

Query OK, 0 rows affected (3.37 sec)

In this way, you can create student table. Note that ,a column can hold NULL. If you do not want to allow NULL values in a column, then simply place constraint on column and write NOT NULL. Here, columns STUDETN_ID,LAST_NAME,FIRST_NAME can not include NULL values. NOT NULL is constraint and it ensures that a column can not have NULL value. While creating tables, we may need to apply certain condition on columns like this column can not be blank or NULL for any record. To apply conditions on columns, SQL constraints are used.

MySQL INSERT multiple rows statement

INSERT statement can be used to insert one or more rows into table. To insert multiple rows into table, INSERT takes following syntax:

INSERT INTO <TABLE NAME>[ < COLUMN LIST> ]
VALUES (<VALUE>,<VALUES>,<VALUES>),
VALUES (<VALUE>,<VALUE>,<VALUE>),
.........

For example, to enter rows into student table,you could use the following statement

mysql> INSERT INTO STUDENT(STUDENT_ID,LAST_NAME,FIRST_NAME,SCORE)
    -> VALUES
    -> (10,"WANG","PEDRO",80),
    -> (11,"DIXT","ATHANG",89),
    -> (12,"PATIL","LUMBINI",40),
    -> (13,"NAIK","VAISHALI",60),
    -> (14,"BHAGAT","SUJATA",90)
    -> ;
Query OK, 5 rows affected (0.40 sec)
Records: 5  Duplicates: 0  Warnings: 0

You can use following SELECT statement to verify the insert

mysql> SELECT * FROM STUDENT;
+------------+-----------+------------+-------+
| STUDENT_ID | LAST_NAME | FIRST_NAME | SCORE |
+------------+-----------+------------+-------+
|         10 | WANG      | PEDRO      |    80 |
|         11 | DIXT      | ATHANG     |    89 |
|         12 | PATIL     | LUMBINI    |    40 |
|         13 | NAIK      | VAISHALI   |    60 |
|         14 | BHAGAT    | SUJATA     |    90 |
+------------+-----------+------------+-------+
5 rows in set (0.08 sec)