MySQL - INSERT Statement

Previous
Quiz
Next

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

mysql> CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Following query inserts a row in the above created table −

insert into sales (ID, ProductName, CustomerName, DispatchDate, DeliveryTime, Price, Location) 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');
insert into sales values(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values(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 −

mysql> SELECT * FROM SALES;
+------+-------------+--------------+--------------+--------------+-------+----------------+
| ID   | ProductName | CustomerName | DispatchDate | DeliveryTime | 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            |
+------+-------------+--------------+--------------+--------------+-------+----------------+
5 rows in set (0.00 sec)

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 = value2 ......;

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) −

mysql> INSERT INTO
SALES SET ID = 6,
ProductName = 'Speaker',
CustomerName = 'Rahman',
Price = 5500;
Query OK, 1 row affected (0.13 sec)

Verification

If you retrieve the contents of the SALES table using the SELECT statement you can observe the inserted row as shown below

mysql> SELECT * FROM SALES;
+------+-------------+--------------+--------------+--------------+-------+----------------+
| ID   | ProductName | CustomerName | DispatchDate | DeliveryTime | 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            |
| 6    | Speaker     | Rahman       | NULL         | NULL         | 5500  | NULL           |
+------+-------------+--------------+--------------+--------------+-------+----------------+
6 rows in set (0.24 sec)

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 −

mysql> 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 −

mysql> 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');
Query OK, 1 row affected (0.84 sec)
mysql> insert into SALES_DETAILS values(2, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai', 30, '90000123654', 'Chennai- TNagar', 'vanajarani@gmail.com');
Query OK, 1 row affected (1.84 sec)

If we want another table with just the contact details of the customer create a table as −

mysql> 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 −

mysql> 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';
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0
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';
Query OK, 1 row affected (0.16 sec)

Verification

You can verify the contents of the CustContactDetails table as shown below −

mysql> SELECT * FROM CustContactDetails;
+------+--------+------+-------------+----------------------+----------------------+
| ID   | Name   | Age  | Phone       | Address              | Email                |
+------+--------+------+-------------+----------------------+----------------------+
| 1    | Raja   | 25   | 9000012345  | Hyderabad – Madhapur | pujasharma@gmail.com |
| 2    | Vanaja | 30   | 90000123654 | Chennai- TNagar      | vanajarani@gmail.com |
+------+--------+------+-------------+----------------------+----------------------+
2 rows in set (0.00 sec)

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 and inserted 4 records int it as shown below −

mysql> Create table Student(Name Varchar(35), age INT, Score INT);
Query OK, 0 rows affected (1.28 sec)
mysql> INSERT INTO student values ('Jeevan', 22, 8);
mysql> INSERT INTO student values ('Raghav', 26, –3);
mysql> INSERT INTO student values ('Khaleel', 21, –9);
mysql> INSERT INTO student values ('Deva', 30, 9);

Suppose we have another table with name columns and types created as −

mysql> Create table Data(Name Varchar(35), age INT, Score INT);
Query OK, 0 rows affected (0.94 sec)

Following query inserts the contents of the Student table into the table Data −

mysql> INSERT INTO Data TABLE Student;
Query OK, 4 rows affected (0.33 sec)
Records: 4 Duplicates: 0 Warnings: 0

Verification

If you verify the contents of the Data table using the SELECT statement you can observe the inserted data as −

mysql> SELECT * FROM data;
+---------+------+-------+
| Name    | age  | Score |
+---------+------+-------+
| Jeevan  | 22   | 8     |
| Raghav  | 26   | –3    |
| Khaleel | 21   | –9    |
| Deva    | 30   | 9     |
+---------+------+-------+
4 rows in set (0.00 sec)

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 −

mysql> CREATE TABLE empData (ID INT UNIQUE, Name VARCHAR(15), email VARCHAR(15), salary INT);
Query OK, 0 rows affected (2.09 sec)

Following query inserts the records in the above table using the update clause −

mysql> INSERT INTO empData VALUES (1, 'Raja', 'raja@gmail.com', 2215)
ON DUPLICATE KEY UPDATE salary = salary+ salary;
Query OK, 1 row affected (0.14 sec)

After this insert contents of the empData table will be as shown below −

mysql> SELECT * FROM empData;
+------+------+----------------+--------+
| ID   | Name | email          | salary |
+------+------+----------------+--------+
| 1    | Raja | raja@gmail.com | 2215   |
+------+------+----------------+--------+
1 row in set (0.07 sec)

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 −

mysql> INSERT INTO empData VALUES (1, 'Raja', 'raja@gmail.com', 2215)
ON DUPLICATE KEY UPDATE salary = salary+ salary;
Query OK, 2 rows affected (0.16 sec)

After this insert contents of the empData table will be as shown below −

mysql> SELECT * FROM empData;
+------+------+----------------+--------+
| ID   | Name | email          | salary |
+------+------+----------------+--------+
| 1    | Raja | raja@gmail.com | 4430   |
+------+------+----------------+--------+
1 row in set (0.00 sec)
mysql_statements_reference.htm
Print Page
Previous
Next
Advertisements