MySQL - 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),
   DisDate 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 −

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 −

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),
   DisDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255),
   CustomerAge INT,
   CustomrtPhone BIGINT,
   DisAddress 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','[email protected]'),
(2, 'Mobile','Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai',30,'90000123654','Chennai- TNagar','[email protected]');

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, DisAddress, Email
FROM SALES_DETAILS
WHERE ID = 1 AND CustomerName = 'Raja';

INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email)
SELECT 
ID, CustomerName, CustomerAge, CustomrtPhone, DisAddress, 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 −

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 −

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', '[email protected]', 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 −

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', '[email protected]', 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 −