MySQL - INSERT Statement

Table of content

MySQL INSERT Statement
INSERT ... SET
INSERT .... SELECT
INSERT ... TABLE
INSERT ... ON DUPLICATE KEY UPDATE Statement

Quiz

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

Print Page