Articles in MySQL Category

MySQL Insert Statement

Insert statements are used for inserting data (adding new rows) into MySQL tables. They have following syntax.

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...);
  • Column count and value count should be same.
  • Columns need not be in their defined order (as in table definition).
  • If a column has a default value (auto-increment, NULL etc), that column can be omitted.
  • It’s a good practice to enclose table name and column names with backtick (~).
  • Except numeric values and NULL, other values need to be enclosed with single quote (’).

Consider a data table with following table definition (For most examples, you will need to truncate the table first).

CREATE TABLE `employee` (
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`id`)

Following is a valid insert statement for this table. You can run this statement in MySQL command-line, in a GUI tool or with mysqli_query in a PHP script.

INSERT INTO `employee` (`first_name`, `last_name`) VALUES ('Robin', 'Jackman');
  • Specifying values for `id`, `job_title`, `salary` and `notes` was not required since they had default values.
  • `first_name` and `last_name` columns can not be null and they don’t have default values. Therefore all the insert statements of `employee` table should contain values for these two columns.

Alternative Syntaxes

If you are going to insert values for all the columns in order, you can omit mentioning column names and have following syntax.

INSERT INTO table_name VALUES (value1, value2,...);

This syntax is not recommended because you have to mention values for all the columns (even though some may have default values) and column order needs to be kept.

In following syntax, columns and values are paired.

INSERT INTO table_name SET column1 = value1, column2 = value2,...;

Below is an example for this syntax.

INSERT INTO `employee` SET `first_name` = 'Robin', `last_name` = 'Jackman';

Inserting Multiple Rows

You can insert multiple rows by providing a parenthesized list of values separated by commas like below.

INSERT INTO `employee` (`first_name`, `last_name`) VALUES ('Robin', 'Jackman'), ('Taylor', 'Edward');

When you have multiple rows to insert, this method is recommended over executing separate insert statements since it is efficient and reduces the load on the server.

Handling Duplicates

Provided that there is already a row in `employee` table with `id` 1, following insert statement will throw an error (for Robin Jackman’s row) since `id` is a unique value column.

INSERT INTO `employee` (`id`, `first_name`, `last_name`) VALUES (1, 'Robin', 'Jackman'), (2, 'Taylor', 'Edward');

In cases where there can be duplicate keys like above, you have three options.


Ignore the inserting of rows with duplicate keys and insert rest of the rows with INSERT IGNORE INTO statement.

INSERT IGNORE INTO `employee` (`id`, `first_name`, `last_name`) VALUES (1, 'Robin', 'Jackman'), (2, 'Taylor', 'Edward');

In above example, if there is already a row with `id` 1, first row will be ignored but second row will be added.


Replace the current rows of duplicate keys and insert new rows with REPLACE INTO statement.

REPLACE INTO `employee` (`id`, `first_name`, `last_name`) VALUES (1, 'Robin', 'Jackman'), (2, 'Taylor', 'Edward');

In above example, if there is already a row with `id` 1 (say Vivian Dickens’s row), it will be deleted and the new row will be added. Now the row with `id` 1 will contain Robin Jackman’s details.


Update the current row with ON DUPLICATE KEY UPDATE statement.

INSERT INTO `employee` (`id`, `first_name`, `last_name`) VALUES (1, 'Robin', 'Jackman'), (2, 'Taylor', 'Edward') ON DUPLICATE KEY UPDATE `notes` = CONCAT( `notes`, ' Updated record');

In above example, if there is already a row with `id` 1, the `notes` field of it will be appended with ‘Updated record’ and no other field will be affected. That is INSERT statement will be ignored and UPDATE statement will be executed.

CONCAT() is a MySQL function. You may use CONCAT_WS() instead of CONCAT() that allows to have a separator.

Inserting from Another Table

Think that you created a new table called `new_employee` and want to import data from `employee` table. You can import like below with a SELECT statement.

INSERT INTO `new_employee` (`first_name`, `last_name`) SELECT `first_name`, `last_name` FROM `employee`;

In above example, schema (table definition) of `employee` and `new_employee` don’t have to be same. Number of columns specified in SELECT statement should be equal to that of INSERT statement and data types should match.

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.