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.