How to retrieve MYSQL records as an INSERT statement.

Question

I'm trying come up with the best method of synchronizing particular rows of 2 different database tables. So, for example there's 2 product tables in different databases as such...

Origin Database

product{
    merchant_id,
    product_id,
    ... additional fields
}

Destination Database

product{
    merchant_id
    product_id
    ... additional fields
}

So, the database schema is the same for both. However I'm looking to select records with a particular merchant_id, remove all records from the destination table that have that merchant_id and replace those records with records from the origin database of the same merchant_id.

My first thought was using mysqldump, parsing out the create table statements, and only running the Insert Statements. Seems like a pain though. So I was wondering if there is a better technique to do this.

I would think mysql has some method of creating INSERT statements as output from a SELECT statement, so you can define how to insert specific record information into a new db.

Any help would be appreciated, thank you much.

Answer 1

phpMyAdmin has part of this capability: You can run a query and then export the results of that query into a file containing CREATE statements.

Update: And mysqldump has it too: Link

mysqldump -u username -p --where="id='merchant_id'" databasename

In regards to replacing merchant IDs, that part I don't entirely understand yet. You may be better off doing a manual search+replace on them. Can you make a real life example of two such records?