SQL Insert multiple rows
This article will deal with a new insertion method in SQL which inserts multiple rows at a time.
Multiple records can be inserted at a time into a specific table with a single Insert into values statement. Observe the below examples to clarify how multiple records are inserted into a specific table.
Examples of Inserting Multiple Rows
Example 1: Let us create a table named student which holds all the basic details of students like student_id, and student_name, and insert multiple records into it.
CREATE TABLE student(
student_id INT(50),
student_address VARCHAR(100),
);
Let us insert data into this table by an insert into the command
INSERT INTO student VALUES
(51,’ 600354 Panipat Haryana’),
(52, ‘500012 Hyderabad Telangana’),
(53, ‘799232 Cochin Kerala’),
(54, ‘899432, Madurai, Tamil Nadu’),
(55, ‘500072 Hyderabad Telangana’),
(56,’400034 Vizag Andhra Pradesh),
(57,’600043 Gurgram Haryana’),
(58, ‘394234 Vijaywada Andhra Pradesh’),
(59, ‘147653 Bhopal Madhya Pradesh’),
(60, ‘209431 Lucknow Uttar Pradesh’);
We can check the data of table by using the following command:
SELECT * FROM student;
The table will look like this:
Output:
| student_id | student_address |
| 51 | 600354 Panipat Haryana |
| 52 | 500012 Hyderabad Telangana |
| 53 | 799232 Cochin Kerala |
| 54 | 899432 Madurai Tamil Nadu |
| 55 | 500072 Hyderabad Telangana |
| 56 | 400034 Vizag Andhra Pradesh |
| 57 | 600043 Gurgram Haryana |
| 58 | 394234 Vijayawada Andhra Pradesh’ |
| 59 | 147653 Bhopal Madhya Pradesh |
| 60 | 209431 Lucknow Uttar Pradesh’ |
Example 2:
Let us create a table called saletime which consists of customer time, product time, and the time at which the sale happened.
CREATE TABLE Saletime(
ProductName VARCHAR(205),
CustomerName VARCHAR(205),
DispatchTimeStamp VARCHAR(205),
Price INT,
Location VARCHAR(205)
);
INSERT INTO Saletime VALUES
('Mouse', 'Altaf', TIMESTAMP('2020-04-04', '12:02:25.247552'), 1000, 'Nizamabad'),
('Laptop’, 'Pradeep', TIMESTAMP('2019-08-06', '04:33:22.721521'), 60000, 'Hyderabad'),
('Wireless_keyboard', 'Bhanu', TIMESTAMP('2016-02-27', '04:20:27.263420'), 2000, 'Vijayawada'),
('Mobile', 'Vani', TIMESTAMP ('2018-05-22', '10:20:25.265563'), 10000, 'Cochin’),
('Headset', 'Ram', TIMESTAMP('2022-02-20', '11:39:37.522321'), 5000, 'Delhi'),
('Earphones', 'Rahim' , TIMESTAMP('2021-11-29', '09:19:23.432356'), 2000, 'Lucknow'),
('Harddisk', 'Charan' , TIMESTAMP('2019-12-30', '12:54:25.122386'), 7000, 'Bangalore');
We can check the data of table by using the following command:
SELECT * FROM Saletime;
The table will look like this:
Output:
| ProductName | CustomerName | DispatchTimeStamp | Price | Location |
| Mouse | Altaf | 2020-04-04 12:02:25.247552 | 1000 | Nizamabad |
| Laptop | Pradeep | 2019-08-06 04:33:22.721521 | 60000 | Hyderabad |
| Wireless_keyboard | Bhanu | 2016-02-27 04:20:27.263420 | 2000 | Vijayawada |
| Mobile | Vani | 2018-05-22 10:20:25.265563 | 10000 | Cochin |
| Headset | Ram | 2022-02-20 11:39:37.522321 | 5000 | Delhi |
| Earphones | Rahim | 2021-11-29 09:19:23.432356 | 2000 | Lucknow |
| Harddisk | Charan | 2019-12-30 12:54:25.122386 | 7000 | Bangalore |
Example 3:
Let us create a table called player_desc which contains all the basic information of a player like player name, player time, player home city, etc.
CREATE TABLE player_desc(
Player_id INT AUTO_INCREMENT,
Player_name VARCHAR(100) NOT NULL,
Player_team VARCHAR(20) NOT NULL,
Player_homecity Varchar(20) NOT NULL,
PRIMARY KEY(Player_id)
);
Let us insert data into this table by an insert into command
INSERT INTO player_desc VALUES
('Virat Kohli', 'India', ‘Delhi’),
('David Warner, 'Australia, 'Paddington’),
('SKY', 'India', ’Mumbai’),
('MS Dhoni', 'India’, ‘Ranchi’),
('JOS Butler', 'England', ‘Taunton’),
('Steve Smith', 'Australia', ‘Saint George’),
('Glenn Philipps ', 'New Zealand', ‘East London’),
('Glenn Maxwell', 'Australia', ‘Kew Australia’),
('Rishabh Pant', 'India', ‘Roorkee’),
(‘ABD’, ‘South Africa’, ‘Transvaal Province’),
(‘Eoin Morgan’, ‘England’, ‘Dublin’),
(‘Dwayne Bravo’, ‘West Indies’, Trinidad’),
(‘Ravindra Jadeja’, ‘India’. ‘Navagam-Khed’),
(‘Suresh Raina’, ‘India’, ’Muradnagar’);
We can check the data of table by using the following command:
SELECT * FROM player_desc;
The table will look like:
Output:
| Player_id | Player_name | Player_team | Player_homecity |
| 1 | Virat Kohli | India | Delhi |
| 2 | David Warner | Australia | Paddington |
| 3 | SKY | India | Mumbai |
| 4 | MS Dhoni | India | Ranchi |
| 5 | Jos butler | England | Taunton |
| 6 | Steve Smith | Australia | Saint George |
| 7 | Glenn Philipps | New Zealand | East London |
| 8 | Glenn Maxwell | Australia | Kew Australia |
| 9 | Rishabh Pant | India | Roorkee |
| 10 | ABD | South Africa | Transvaal Province |
| 11 | Eoin Morgan | England | Dublin |
| 12 | Dwayne Bravo | West Indies | Trinidad |
| 13 | Ravindra Jadeja | India | Navagam-Khed |
| 14 | Suresh Raina | India | Muradnagar |
Example 4:
Let us create a table named Subscription info which consists of information different types of subscriptions which are purchased by various customers.
create table Subscription_info(customer_name VARCHAR(200), Subscription_ info VARCHAR(200) purchasetime VARCHAR(200));
Now, insert some data in the subscription_info table.
insert into Subscription_info
values('Priyanka', 'Gold', ‘03:13:20’),
('Abrar', 'Basic', ‘02: 23:39’),
('Karthik', 'Premium', ‘05:45:30’),
('Mahesh', 'Basic', ‘06:30:32’),
('Surya', 'Premium', ‘11:25:35’),
('Lokesh', 'Basic', ‘10:32:50’),
('Keerthi', 'Gold', ‘09:22:55’),
('Rakesh', 'Premium', ‘11:42:53’),
(‘Vamshi’, ‘Basic’, ‘12:30:00’),
(‘Rahul’, ‘Basic’, ‘10:55:00’);
We can check the data of table by using the following command:
SELECT * FROM Subscription_info;
Output:
| customer_name | Subscription_info | purchasetime |
| Priyanka | Gold | 03:13:20 |
| Abrar | Basic | 02:23:39 |
| Karthik | Premium | 05:45:30 |
| Mahesh | Basic | 06:30:32 |
| Surya | Premium | 11:25:35 |
| Lokesh | Basic | 10:32:50 |
| Keerthi | Gold | 09:22:55 |
| Rakesh | Premium | 11:42:53 |
| Vamshi | Basic | 12:30:00 |
| Rahul | Basic | 10:55:00 |
Example 5:
Consider the below table called employee and create attributes like emp_id, emp_name, and department.
CREATE TABLE employee( emp_id INT AUTO_INCREMENT,
emp_name VARCHAR (100),
department VARCHAR(200));
Now, insert some data of employees in the employee table.
INSERT INTO employee VALUES(‘Suresh’, ‘Executive’),
(‘Shreya Singh’, ‘Operations’),
(‘Kavitha Kulkarni ’, ‘Human resources),
(‘Raj Pawar’, ‘Marketing’),
(‘Jay Sharma’, ‘Research and development),
(‘Sravan Kumar’, ‘Operations’),
(‘Ranjith Kumar’, ‘Executive'),
(‘Harish’, ‘Research and development),
(‘Rajnish’, ‘Marketing’),
(‘Pritam Sharma’, ‘Executive’);
We can check the data of table by using the following command:
SELECT * FROM employee;
Output:
| emp_id | emp_name | department |
| 1 | Suresh | Executive |
| 2 | Shreya Singh | Operations |
| 3 | Kavitha Kulkarni | Human resources |
| 4 | Raj Pawar | Marketing |
| 5 | Jay Sharma | Research and development |
| 6 | Sravan Kumar | Operations |
| 7 | Ranjith Kumar | Executive |
| 8 | Harish | Research and development |
| 9 | Rajnish | Marketing |
| 10 | Pritam Sharma | Executive |
This is all about inserting multiple rows in MYSQL. Hope you understood this topic.