3

Inserting from one table to another with identical format, or Importing a csv file into an existing table. If there is a duplicate primary key detected, instead of updating the existing row, I want the duplicate to be added as a new row with a new key. Is this possible with ON DUPLICATE ?

John Wilcock
  • 31
  • 2

1 Answers1

0

Is this possible with ON DUPLICATE ?

No, the command is ON DUPLICATE KEY UPDATE, there is no ON DUPLICATE KEY INSERT.

You have options.

  1. You can use a store procedure to acomplish exactly that. As described on this answer.
  2. If you just need to populate the table and you dont care about the ids being referenced in another table at the time of the import (as it seems because you will be generating new ones for some of them) do the import without inserting the id yourself. You should have your id as autoincrement and the database will assign new ones for each record, there is no need to check for duplicate ids at this point.
  3. If, for some reason, you need to preserve the original ids of all but the duplicates. I strongly suggest to split your excel: one without any duplicates, one with only the duplicates. This way it will be so easy to import the first one with original ids, and import the second one without them (so the system generates one for each of them).
Community
  • 1
  • 1
Francisco Félix
  • 2,413
  • 13
  • 16
  • Thank you. I am trying to import data from a csv file using workbench. I get errors with Key. If I enter Key values into the csv file before importing it works fine. If I leave the Key column empty I get an error. If I don't include a Key column in the csv I get an error as well. I would like to be able to import the csv without having to enter Key values first. Can you explain what I may be doing wrong? The error I get is: 1366: Incorrect integer value: "for column 'Key' at row 1 – John Wilcock Jul 23 '15 at 21:33
  • You are welcome. Wrong data type or a constraint violation. What i do is import the excel file to a new table, therefore i have no problems at all in this first step (the table replicates the excel worksheet). Then i run a simple query inserting every row of that temprary table in the destination table. The destination table has the id as int and autoincrement and as i dont give it any id it generates one for every row. Its a one line query. – Francisco Félix Jul 23 '15 at 23:50