mysql insert multiple rows, return rows that failed

Question

When I try to insert (lets say) 30 rows in my table.

For example

    INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   ), ... ;

And cust_name has to be unique. How can I then identify the records that failed to insert because their cust_name already exists?

Is it possible to return them?

Answer 1

as far as i know this is not possible.

if you are using another language to insert your data, you can insert one row at once and loop that. this way you can easily tell which rows have failed.

Answer 2

If you are inserting multiple records in one query you won't be able to easily find out which record failed to add. You could however change this to 30 statements, each inserting just one record and then if one failes you will instantly get which was it.

Answer 3

Without an IGNORE clause tagged onto your insert statement, I would expect the command to fail if you try to insert duplicates. With an IGNORE clause, the statement returns an information string reporting the number of records, duplicates, and warnings.

For more detailed information, please have a look at

http://dev.mysql.com/doc/refman/5.1/en/insert.html

and search for "Duplicates" on the page. That will take you exactly to the spot where this is explained in detail. However, information on which of the rows that have failed cannot be obtained in this way, and I do not know of any other method to achieve this. The mysqlimport utility and the LOAD DATA INFILE statement do not have such an option either.

Therefore, if I really needed to do that I would write a script for it.

Answer 4

I am not sure if you are using dummy data in there but is there any reason cust_name has to be unique, I can understand a ID key or something but surely there will be times where names clash. The phonebook is full of dupe entries, have you got instead cust_id on your table you could use instead to identify?

Answer 5

Don't know about your requirements, but having unique customer name is NOT A GOOD idea

Answer 6

You can use 3 different strategies in order to handle UNIQUE index or PRIMARY KEY errors in multiple insertion:

1. If you use the IGNORE keyword in your INSERT statement, errors that occur while executing the INSERT statement are treated as warnings instead and the other rows will be inserted.

2. If you specify ON DUPLICATE KEY UPDATE keyword in your INSERT statement, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed as you mention in your statement, for example you can add '(old)' to the previously inserted Customer's name in your special scenario.

3. If you use REPLACE keyword instead of INSERT, in case of a duplicate value in a UNIQUE index or PRIMARY KEY, the previously inserted row would be deleted and the new row would be inserted instead.

read more at:

http://dev.mysql.com/doc/refman/5.1/en/insert.html

Good Luck.