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.

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.

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

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 -