I am googling around and reading posts on this site but not clear what I should I do go with insert unique, records.
I basically have a giant file that that has a single column of data in it that needs to be imported into a db table where several thousand of the records from my text file already exist.
There are no id's in the text file I need to import and what I am reading insert ignore looks to be solving for duplicate ID's. I want new ids created for any of the new records added but obviously I can't have duplicates.
This would ideally be done with load data infile...but really not sure:
Insert IGNORE? ON Duplicate Key?
The easiest way to achieve what you want is to read in the entire file using LOAD DATA, and then insert all non duplicates into the table which already exists.
LOAD DATA LOCAL INFILE 'large_file.txt' INTO TABLE new_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(name);
This assumes that the first line of your giant file does not contain a column header. If it does, then you can add IGNORE 1 LINES to the statement above.
You can now INSERT the names in new_table into your already-existing table using INSERT INTO IGNORE:
INSERT IGNORE INTO your_table_archive (name)
SELECT name
FROM new_table
Any duplicate records which MySQL encounters will not be inserted, and instead the original ones will be retained. And you can drop new_table once you have what you need from it.