MySQL LOAD DATA INFILE Load data with duplicates
Example
If you use the LOAD DATA INFILE command to populate a table with existing data, you will often find that the import fails due to duplicates. There are several possible ways to overcome this problem.
LOAD DATA LOCAL
If this option has been enabled in your server, it can be used to load a file that exists on the client computer rather than the server. A side effect is that duplicate rows for unique values are ignored.
LOAD DATA LOCAL INFILE 'path of the file/file_name.txt'
INTO TABLE employee
LOAD DATA INFILE 'fname' REPLACE
When the replace keyword is used duplicate unique or primary keys will result in the existing row being replaced with new ones
LOAD DATA INFILE 'path of the file/file_name.txt'
REPLACE INTO TABLE employee
LOAD DATA INFILE 'fname' IGNORE
The opposite of REPLACE, existing rows will be preserved and new ones ignored. This behavior is similar to LOCAL described above. However the file need not exist on the client computer.
LOAD DATA INFILE 'path of the file/file_name.txt'
IGNORE INTO TABLE employee
Load via intermediary table
Sometimes ignoring or replacing all duplicates may not be the ideal option. You may need to make decisions based on the contents of other columns. In that case the best option is to load into an intermediary table and transfer from there.
INSERT INTO employee SELECT * FROM intermediary WHERE ...
