Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Insert Query

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

INSERT Statement
MySQL INSERT Statement
You can add new rows to an existing table of MySQL using the INSERT statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names).
Syntax
Following is the syntax of the INSERT statement of MySQL.
INSERT INTO table_name (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Where, table_name is the name of the table into which you need to insert data, (column1, column2, column3,...columnN) are the names of the columns and (value1, value2, value3,...valueN) are the values in the record.
Example
Assume we have created a table with name Sales in MySQL database using CREATE TABLE statement as shown below
CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);
Following query inserts a row in the above created table 
Insert into sales values
(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 
'Hyderabad');
If you pass the values to the INSERT statement in the same order as in the table you can omit the column names 
Insert into sales values(2, 'Earphones', 'Roja', DATE('2019-05-01'),
TIME('11:00:00'), 2000, 'Vishakhapatnam');
Now, let us insert 3 more records in Sales table.
Insert into sales values
(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 
3000, 'Vijayawada'),
(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 
9000, 'Chennai'),
(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 
6000, 'Goa');
Verification
If you verify the contents of the Sales table using the SELECT statement you can observe the inserted records as shown below 
SELECT * FROM SALES;
Output
The above query will produce the following output 
ID
Product Name
Customer Name
Dispatch Date
Delivery Time
Price
Location
1
Key-Board
Raja
2019-09-01
11:00:00
7000
Hyderabad
2
Earphones
Roja
2019-05-01
11:00:00
2000
Vishakhapatnam
3
Mouse
Puja
2019-03-01
10:59:59
3000
Vijayawada
4
Mobile
Vanaja
2019-03-01
10:10:52
9000
Chennai
5
Headset
Jalaja
2019-04-06
11:08:59
6000
Goa
INSERT ... SET
You can insert a record by setting values to selected columns using the INSERT...SET statement. Following is the syntax of this statement 
INSERT INTO table_name SET column_name1 = value1, column_name2=value2s...;
Where, table_name is the name of the table into which you need to insert the record and column_name1 = value1, column_name2 = value2 ...... are the selected column names and the respective values.
If you insert record using this statement the values of other columns will be null.
Example
Following query inserts a record into the SALES table using the INSERT...SET statement. Here, we are passing values only to the ProductName, CustomerName and Price columns (remaining values will be NULL) 
INSERT INTO
SALES SET ID = 6,
ProductName = 'Speaker',
CustomerName = 'Rahman',
Price = 5500;
Verification
If you retrieve the contents of the SALES table using the SELECT statement you can observe the inserted row as shown below
SELECT * FROM SALES;
Output
Following is the output of the above program 
ID
Product Name
Customer Name
Dispatch Date
Delivery Time
Price
Location
1
Key-Board
Raja
2019-09-01
11:00:00
7000
Hyderabad
2
Earphones
Roja
2019-05-01
11:00:00
2000
Vishakhapatnam
3
Mouse
Vanaja
2019-03-01
10:59:59
3000
Vijayawada
4
Mobile
Vanaja
2019-03-01
10:10:52
9000
Chennai
5
Headset
Jalaja
2019-04-06
11:08:59
6000
Goa
6
Speaker
Rahman
NULL
NULL
5500
NULL
INSERT .... SELECT
You can select desired column values from one table and insert them as a record into another table using the INSERT .... SELECT statement following is the syntax to do so 
INSERT INTO table_to (column1, column2,....)
SELECT Column1, column2 .....
FROM Table_from
WHERE condition
Example
Suppose we have created a table that contains the sales details along with the contact details of the customers as shown below 
CREATE TABLE SALES_DETAILS (
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255),
   CustomerAge INT,
   CustomrtPhone BIGINT,
   DispatchAddress VARCHAR(255),
   Email VARCHAR(50)
);
Now, let's insert 2 records into the above created table using the INSERT statement as 
Insert into SALES_DETAILS values
(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 
'Hyderabad',25,'9000012345','Hyderabad - Madhapur','pujasharma@gmail.com'),
(2, 'Mobile','Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai',30,'90000123654','Chennai- TNagar','vanajarani@gmail.com');
If we want another table with just the contact details of the customer create a table as 
CREATE TABLE CustContactDetails (
   ID INT,
   Name VARCHAR(255),
   Age INT,
   Phone BIGINT,
   Address VARCHAR(255),
   Email VARCHAR(50)
);
Following query insets records into the CustContactDetails table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the SALES_DETAILS table to CustContactDetails table 
INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email)
SELECT
ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email
FROM SALES_DETAILS
WHERE ID = 1 AND CustomerName = 'Raja';
﻿
INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email)
SELECT 
ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email
FROM SALES_DETAILS
WHERE ID = 2 AND CustomerName = 'Vanaja';
Verification
You can verify the contents of the CustContactDetails table as shown below 
SELECT * FROM CustContactDetails;
Output
The above mysql query will generate the following output 
ID
Name
Age
Phone
Address
Email
1
Raja
25
9000012345
Hyderabad - Madhapur
pujasharma@gmail.com
2
Vanaja
30
90000123654
Chennai - TNagar
vanajarani@gmail.com
INSERT ... TABLE
On the other hand, instead of selecting specific columns you can insert the contents of one table into another using the INSERT... TABLE statement. Following is the syntax to do so 
INSERT INTO table1 TABLE table2;
Example
Assume we have created a table with name student using the following CREATE statement 
Create table Student(
   Name Varchar(35), 
   age INT, 
   Score INT
);
Now, let us insert four records into the student table 
INSERT INTO student values 
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Khaleel', 21, -9),
('Deva', 30, 9);
Suppose we have another table with name columns and types created as 
Create table Data(
   Name Varchar(35), 
   Age INT, 
   Score INT
);
Following query inserts the contents of the Student table into the table Data 
INSERT INTO Data TABLE Student;
Verification
If you verify the contents of the Data table using the SELECT statement you can observe the inserted data as 
SELECT * FROM data;
Output
The above query will produce the following output 
Name
Age
Score
Jeevan
22
8
Raghav
26
-3
Khaleel
21
-9
Deva
30
9
INSERT ... ON DUPLICATE KEY UPDATE Statement
If one of the columns of a table is has a UNIQUE of PRIMARY KEY constraint and, If you use ON DUPLICATE KEY UPDATE clause along with the INSERT statement to insert a record in that particular table, if the value passed under the column with the either of the constrains is duplicate, instead of adding a new record the old record will be updated.
Syntax
Following is the syntax of the INSERT ... ON DUPLICATE KEY UPDATE Statement 
INSERT INTO table_name (column1, column2,....) 
VALUES (value1, value2,....) ON DUPLICATE KEY UPDATE update_statement;
Example
Assume we have created a table name empData and declare the ID column as UNIQUE as 
CREATE TABLE empData (
   ID INT UNIQUE, 
   Name VARCHAR(15), 
   email VARCHAR(15), 
   salary INT
);
Following query inserts the records in the above table using the update clause 
INSERT INTO empData VALUES (1, 'Raja', 'raja@gmail.com', 2215)
ON DUPLICATE KEY UPDATE salary = salary+ salary;
After this insert contents of the empData table will be as shown below 
SELECT * FROM empData;
Output
Following is the output of the above mysql query 
ID
Name
email
salary
1
Raja
raja@gmail.com
2215
If you execute the above statement again, since the record with ID value 1 already exists instead of inserting new record the salary value in the statement will be added to the existing salary 
INSERT INTO empData VALUES (1, 'Raja', 'raja@gmail.com', 2215)
ON DUPLICATE KEY UPDATE salary = salary+ salary;
After this insert contents of the empData table will be as shown below 
SELECT * FROM empData;
Output
The above query generates the following output 
ID
Name
email
salary
1
Raja
raja@gmail.com
4430
﻿
﻿
﻿
﻿
﻿
﻿

ID	Product Name	Customer Name	Dispatch Date	Delivery Time	Price	Location
1	Key-Board	Raja	2019-09-01	11:00:00	7000	Hyderabad
2	Earphones	Roja	2019-05-01	11:00:00	2000	Vishakhapatnam
3	Mouse	Puja	2019-03-01	10:59:59	3000	Vijayawada
4	Mobile	Vanaja	2019-03-01	10:10:52	9000	Chennai
5	Headset	Jalaja	2019-04-06	11:08:59	6000	Goa

ID	Name	Age	Phone	Address	Email
1	Raja	25	9000012345	Hyderabad - Madhapur	pujasharma@gmail.com
2	Vanaja	30	90000123654	Chennai - TNagar	vanajarani@gmail.com

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

INSERT Statement

MySQL INSERT Statement

Syntax

Example

Verification

Output

INSERT ... SET

Example

Verification

Output

INSERT .... SELECT

Example

Verification

Output

INSERT ... TABLE

Example

Verification

Output

INSERT ... ON DUPLICATE KEY UPDATE Statement

Syntax

Example

Output

Output