MySQL: Difference between "Append" / "Update" when importing data into a table

[The Stigenator]

I am having to do an import of 150 access files into MySQL, as we are moving away. I am using an older version of Navicat 9 - not the current version, the client is too cheap to buy a newer version when the older version works, which I agree - I am running InnoDB for this.

These files are going to be first imported in as individual tables in individual databases, the next step is to consolidate them. However, I ran into an issue. .Navicat allows you to import it as "Append" or "Update" or "Append/Update" or "Copy" ..

Now each option has the following stuff in it as options:
Append: Add records to destination table
update: update record in destination with matching record from source
append/update: if record exists, then update it, if not add it
copy: delete all records, repopulate from source


The issue is speed & accuracy. On a brand new table - the append & update shouldn't have a difference in records from what the original source has, but I find append has a differing number of rows and more errors imported than say update, which seems to go slower but more accurate for the same test tables. That said Append/update is the slowest but it takes out any perceived duplicates. But since that is going to be done in the next step, I just need to get the data tables in first.

Someone suggested I am better off running MongoDB for these are straight text search stuff - something to look into perhaps.

 

[piokos]

Can you write a bit more about the tables? What do you mean by "consolidate"?

I assume you have 150 access files because of file size limit. Right?
So are you just merging rows (same table split over many files, UNION [ALL] operator) or joining by columns?
Because if this is just a simple union, the only reason for an error would be an incorrect column type.

Just keep in mind that if you're not sure what's inside the tables (including the lack of keys), merging tables using any kind of "update" is not commutative, which could serve you a proper disaster.

One thing I would do is absolutely forget about any kind of client functionality for the ETL if you're running into problems so early. Write a pure SQL script. You'll have more control and a much larger community to help you.

 

[ringtail]

This is intentionally simplistic:

You could think of Append as to just add new rows to the bottom of a list of rows that already exist in your table. It's like a simple clerical task - just tack more rows onto the structure that's already established for that table.

You could think of Update as being slightly more powerful. Update can add more rows onto your table, and ALSO can change data in rows that are already in your table.

As for your comments about which interface to use, for starters I nominate 3:
phpmyadmin - it's free (for personal use) and powerful, sooo easy, yes a little slow, but sooo easy,
Toad for MySQL - free for your personal use but your company has to pay. Toad is powerful yet complex. It takes a little time to get on top of Toad. It's sort of like for project monitoring, you can use a geterdone tool like maybe Asana (analogy to something like phpmyadmin) or you can use a tool with overabundant complexity (analogy to Microsoft Project).
MySQL Workbench - might be the fastest, but you need really deep understanding of tiny details about MySQL in order to get it to dance.

After using all these and several other brands, I tend to use phpmyadmin and MySQL Workbench. If you discover a better one please tell us about it on Anandtech!