MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

by

These steps can be used to emulate this functionality:

  1. Create a new temporary table.

    CREATE TEMPORARY TABLE temporary_table LIKE target_table;

  2. Optionally, drop all indices from the temporary table to speed things up.

    SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `some_other_index` ON temporary_table;

  3. Load the CSV into the temporary table

    LOAD DATA INFILE 'your_file.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(field1, field2);

  4. Copy the data using ON DUPLICATE KEY UPDATE

    SHOW COLUMNS FROM target_table;
INSERT INTO target_table
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2);

  5. Remove the temporary table

    DROP TEMPORARY TABLE temporary_table;

Using SHOW INDEX FROM and SHOW COLUMNS FROM this process can be automated for any given table.

More Related Contents:

  1. How do I import CSV file into a MySQL table?
  2. Import data in MySQL from a CSV file using LOAD DATA INFILE
  3. MySQL load NULL values from CSV data
  4. On Duplicate Key Update same as insert
  5. How to import a CSV file into a MySQL table
  6. Importing a csv into mysql via command line
  7. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  8. Import CSV to MySQL
  9. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  10. access denied for load data infile in MySQL
  11. Mysql permission errors with ‘load data’
  12. MySQL load data infile – acceleration?
  13. Too many auto increments with ON DUPLICATE KEY UPDATE
  14. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?
  15. INSERT INTO SELECT statement with more columns which is not in another table
  16. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  17. Why does MYSQL higher LIMIT offset slow the query down?
  18. Normalization in MYSQL
  19. How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?
  20. how to compute similarity between two strings in MYSQL
  21. Duplicating a MySQL table, indices, and data
  22. Cast int to varchar
  23. How to export data from SQL Server 2005 to MySQL [closed]
  24. “where 1=1” statement [duplicate]
  25. mysql select sum group by date
  26. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  27. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  28. MySQL Integer vs DateTime index
  29. Array data type, split string,
  30. How can I make a table in MySQL called “order”?

Leave a Comment