SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

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_idstudent_address
51600354 Panipat Haryana
52500012 Hyderabad Telangana
53799232 Cochin Kerala
54899432  Madurai Tamil Nadu
55500072 Hyderabad Telangana
56400034 Vizag Andhra Pradesh
57600043 Gurgram Haryana
58394234 Vijayawada Andhra Pradesh’
59147653 Bhopal Madhya Pradesh
60209431 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:

ProductNameCustomerNameDispatchTimeStampPriceLocation
MouseAltaf2020-04-04 12:02:25.2475521000Nizamabad
LaptopPradeep2019-08-06 04:33:22.72152160000Hyderabad
Wireless_keyboardBhanu2016-02-27 04:20:27.2634202000Vijayawada
MobileVani2018-05-22 10:20:25.26556310000Cochin
HeadsetRam2022-02-20 11:39:37.5223215000Delhi
EarphonesRahim2021-11-29 09:19:23.4323562000Lucknow
HarddiskCharan2019-12-30 12:54:25.1223867000Bangalore

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_idPlayer_namePlayer_teamPlayer_homecity
1Virat KohliIndiaDelhi
2David WarnerAustraliaPaddington
3SKYIndiaMumbai
4MS DhoniIndiaRanchi
5Jos butlerEnglandTaunton
6Steve SmithAustraliaSaint George
7Glenn PhilippsNew ZealandEast London
8Glenn MaxwellAustraliaKew Australia
9Rishabh PantIndiaRoorkee
10ABDSouth AfricaTransvaal Province
11Eoin MorganEnglandDublin
12Dwayne BravoWest IndiesTrinidad
13Ravindra JadejaIndiaNavagam-Khed
14Suresh RainaIndiaMuradnagar

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_nameSubscription_infopurchasetime
PriyankaGold03:13:20
AbrarBasic02:23:39
KarthikPremium05:45:30
MaheshBasic06:30:32
SuryaPremium11:25:35
LokeshBasic10:32:50
KeerthiGold09:22:55
RakeshPremium11:42:53
VamshiBasic12:30:00
RahulBasic10: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_idemp_namedepartment
1SureshExecutive
2Shreya SinghOperations
3Kavitha KulkarniHuman resources
4Raj PawarMarketing
5Jay SharmaResearch and development
6Sravan KumarOperations
7Ranjith KumarExecutive
8HarishResearch and development
9RajnishMarketing
10Pritam SharmaExecutive

This is all about inserting multiple rows in MYSQL. Hope you understood this topic.