0

How can I import a SQL-file (yes, sql not csv) with phpmyadmin so that it replaces or updates the data while importing? I did not find option for that. I also created another temporary database where I imported the sql-file in question (having only INSERT -lines, only data no structure), and then went to export to select suitable option like INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ..but did not find one or anything that would help in the situation.

So how can I achieve that? If not with phpMyAdmin, is there a program that transforms "insert" sql file to "update on duplicate", or even from "insert" to "delete" after which I could then re-import with original file?

How I came to this, if it helps the above or if someone has better solutions to previous steps: I have a semi-large (1 GB) DB file to import, which I have then divided to multiple smaller files to get it imported. One of them being the structuce sql-dump and rest the data. When still trying to get the large file through, trying to adjust timeout settings through htaccess or phpmyadmin import options did not help - always getting the timeout anyway. Since those did not work, I found a program by Janos Rusiczki (https://rusiczki.net/2007/01/24/sql-dump-file-splitter/) to split the sql file into smaller ones (good program thanks Janos!). It also separated the structure from the data.

However after 8 succesfull imports I got timeout again, after phpmyadmin already imported part of the file. Thus I ended up in current situation. I know, I can always delete all and start over with even smaller partial files, but.. I am sure there is a better way to do this. There has to be a way to replace the files on import, or do some other way described above.

Thanks for any help! :)

Seppo
  • 137
  • 2
  • 9
  • I would recommend you stop using phpmyadmin. You can run long-running data loads by using the command-line client instead. Extra nice is piping the dump file through `pv -pert` first, to create a progress bar. Then into the `mysql` client. – Bill Karwin Jan 23 '18 at 00:38

1 Answers1

0

Cribbing from INSERT ... ON DUPLICATE KEY (do nothing), you can use a regular expression to make every INSERT into an INSERT IGNORE in your sql file and it will pass over all the entries that have already been imported.

Note that will also ignore other errors, but other than timeout errors don't seem likely in this context.

Jerry
  • 3,391
  • 1
  • 19
  • 28