Thesqldump insert data to exists table

MySQLmysqldump

hellow

Dumped x.sql file from db and then
trying to find way to insert data from x.sql file to existent tbl in another server.
But mysql execute all the concept of the x.sql ( that include "drop table if exists tbl and create new")

mysql –host= –user= –password= schema tbl < x.sql

there is away may be to dump tbl (with out "drop and create new" commands only insert )?

Best Answer

If you can rebuild the x.sql, run mysqldump with suitable option such as --no-drop-tables, --no-creates (or whatever they are). Then look at the dump file to make sure there are no surprises in it.

If you can't rebuild x.sql, then edit it to get rid of drops/creates/etc.

If there are no USE statements in x.sql, you can say mysql ... new_db < x.sql (after doing CREATE DATABASE new_sql). Then play SQL games to copy the data from new_db.foo to old_db.foo. (Provide more details if you need help in this area.)

Related Solutions

MySQL – How to Use mysqldump with INSERT … ON DUPLICATE

There are options to help you in this:

  --insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.

Keep this paradigm in mind

mysqldump everything from DB1 into DUMP1
load DUMP1 into DB3
mysqldump everything from DB2 using --replace (or --insert-ignore) and --no-create-info into DUMP2
load DUMP2 into DB3

Thesqldump not generating USE statement

Try putting the USE db_name manually

echo "USE db_name" > output.sql
mysqldump -u root db_name tbl_1 tbl_2 tbl_3 >> output.sql

I can see the wisdom of not mixing the USE command with select mysqldumping of tables. You do not want to risk creating database with the name of a production database. You also do not want the developer to become aware of not the database that will be the target. In this way, the mysqldump, in itself, can never be blamed for overwriting target tables.

Best Answer

Related Solutions

MySQL – How to Use mysqldump with INSERT … ON DUPLICATE

Thesqldump not generating USE statement

Related Question