Home » SQL INSERT Multiple Rows

SQL INSERT Multiple Rows

by Online Tutorials Library

SQL INSERT Multiple Rows

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’.

SQL INSERT Multiple Rows

The student table is created successfully.



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

SQL INSERT Multiple Rows

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

 



ID Name Percentage Location DateOfBirth
1 Manthan Koli 79 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan aspire 90 Chennai 2005-10-02
5 Rahul Khanna 92 Ambala 1996-03-04
6 Pankaj Deshmukh 67 Kanpur 2000-02-02
7 Gaurav Kumar 84 Chandigarh 1998-07-06
8 Sanket Jain 61 Shimla 1990-09-08
9 Sahil Wagh 90 Kolkata 1968-04-03
10 Saurabh Singh 54 Kashmir 1989-01-06

The results show that all ten records are inserted successfully using a single query.

Example 2:

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 items_tbl in the selected database ‘dbs’.



SQL INSERT Multiple Rows

The table named items_tbl is created successfully.

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

SQL INSERT Multiple Rows



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

 

ID Item_Name Item_Quantity Item_Price Purchase_Date
1 Soap 5 200 2021-07-08
2 Toothpaste 2 80 2021-07-10
3 Pen 10 50 2021-07-12
4 Bottle 1 250 2021-07-13
5 Brush 3 90 2021-07-15
6 Notebooks 10 1000 2021-07-26
7 Handkerchief 3 100 2021-07-28
8 Chips Packet 5 50 2021-07-30
9 Marker 2 30 2021-08-13
10 Scissors 1 60 2021-08-13

The results show that all ten records are inserted successfully using a single query.


Next TopicSQL UPDATE

You may also like