0

Would it be possible to append another sql into existing table data using a command like the one below.

$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

This command will overwrite the existing table data.

Looking at appending to existing instead of overwrite.

Any command for this ?

CannedMoose
  • 509
  • 1
  • 10
  • 18
Zubinn Tan
  • 1
  • 4
  • This will execute whatever is contained in your data.sql (just like as if you would execute these statements from wherever you normally do that). If the data.sql-file contains a command to drop and recreate a table and its data, it will do it. (That is often the case when you do a backup). If it would e.g. just contain inserts, it will append the data. – Solarflare Apr 15 '18 at 09:01
  • using this style of command from command prompt, any way to do as you have mentioned ? any switches for this type of command ? mysqldump --user=xxxxxx --password=xxxxxx xxxxxx zzzzzz > aaaaaaa.sql – Zubinn Tan Apr 15 '18 at 09:43
  • `mysqldump --no-create-info` might do what you want. But it will not (on import) check what data is already there; it expects an empty database, and it may or may not work correctly if there is data already. You may have to build a custom import script (that e.g. compares data or adjusts the sql file before import) if you have a more complicated requirement (and the bar for "complicated" is pretty low, as already something as "complicated" as having autoincrement ids might not automatically work as you hope it would) – Solarflare Apr 15 '18 at 15:55

1 Answers1

1

Have a look in the sql file that you want to add to existing data. It is likely a dump that includes the lines starting like this: DROP TABLE IF EXISTS and/or it contains DROP DATABASE IF EXISTS db_name

You need to remove these lines from the sql file that you feed into mysql, either by modifying the dump command with which you created the file (see --skip-add-drop-table option and --no-create-db options) or by removing those lines from the sql file with an editor or awk or something.

see https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

other relevant SO-links: mysqldump with create database line and MySQL import - How to ignore Drop table if exists line?

luksch
  • 11,497
  • 6
  • 38
  • 53
  • i am trying to write an automated batch process using mysqldump --user=xxxxxx --password=xxxxxx xxxxxx zzzzzz > aaaaaaa.sql – Zubinn Tan Apr 15 '18 at 09:43
  • Interesting. I suggest reading up on the command line options of mysql-dump so that you only export what you need. – luksch Jun 04 '18 at 12:55