TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

SQL INSERT Multiple Rows

SQL insert multiple rows with sql, tutorial, examples, insert, update, delete, select, join, database, table, join

<< Back to SQL

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.

mysql> USE dbs;

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

mysql> CREATE TABLE student(ID INT, Name VARCHAR(20), Percentage INT, Location VARCHAR(20), DateOfBirth DATE);

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:

mysql> INSERT INTO student(ID, Name, Percentage, Location, DateOfBirth) VALUES(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 Jaiswal", 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");

SQL INSERT Multiple Rows

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

mysql> SELECT *FROM student;

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

mysql> USE dbs;

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

mysql> CREATE TABLE items_tbl(ID INT, Item_Name VARCHAR(20), Item_Quantity INT, Item_Price INT, Purchase_Date DATE);

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:

mysql> INSERT INTO items_tbl(ID, Item_Name, Item_Quantity, Item_Price, Purchase_Date) VALUES(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");

SQL INSERT Multiple Rows

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

mysql> SELECT *FROM items_tbl;

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




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf