Summary -

In this topic, we described about the INSERT IGNORE with detailed example.

INSERT IGNORE statement is used to skip the error messages while inserting rows into table.

INSERT IGNORE Statement not supported in all databases. INSERT IGNORE statement works only in MySQL database.

For example, let us assume employee_details table defined with UNIQUE emp_id and it won't allow to insert duplicate values. While we try to insert duplicate rows with the duplicate values, system throws an error and insert gets failed. By using INSERT with IGNORE statement, we can ignore the errors that occurs during the INSERT operation. INSERT with IGNORE shows a simple warning message for duplicate row.

Syntax -

INSERT IGNORE INTO table_name
 (column1, column2, …, columnN)
 VALUES (value1, value2, …, valueN); 
  • column1, column2, …, columnN - Specifies the column names that are used to insert data.
  • table_name – Specifies the name of the table.
  • value1, value2, …, valueN – specifies the values that are inserting into table.

We can use INSERT IGNORE statement to avoid errors in the following scenarios.

  • Assuming the INSERT have duplicate values for the columns which are part of PRIMARY or UNIQUE constraints.
  • Assuming the INSERT have NULL values to the column which was defined with NOT NULL constraint.
  • Assuming the INSERT have values to the column in partitioned table where entered values do not match the format of partitioned table.

Example –

Let us consider an employee_details table with UNIQUE emp_id column as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Insert a row with duplicate value.

Requirement – Insert a row with emp_id as 005 into employee_details table. The query was as follows –

INSERT IGNORE INTO employee_details VALUES(005,"Employee8","Salesman", 
 "Employee4", "2019-11-25", 17000, 1000); 

By executing above query, we cannot insert row into employee_details table and instead of showing error, it shows a warning. The result is as follows –

query OK, 0 rows effected, 1 warning .

warning message duplicate value 005 on emp_id.


Scenario – Insert multiple rows with one duplicate value.

Requirement – Insert two valid employee values and one with duplicate emp_id value row in employee_details table. The query was as follows –

INSERT IGNORE INTO employee_details VALUES(010,"Employee10","Salesman",
"Employee4", "2019-09-09", 14000, 2000),( 011, "Employee11", "Salesman",
"Employee4", "2019-10-08", 13000, 2000),(011, "Employee11", "Employee5",
 "2019-11-25", 17000, 1000); 

By executing above query, we can insert two employee rows in employee_details by skipping duplicate row by showing warning in the output. The result was as follows –

query OK, 2 rows effected, 1 warning .

warning message duplicate value 011 on emp_id.


The table after inserting was like below -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee10 Salesman Employee4 2019-09-09 14000.00 2000
011 Employee11 Salesman Employee4 2019-10-08 13000.00 2000