Get Insert Statement for existing row in MySQL

Question

Using MySQL I can run the query:

SHOW CREATE TABLE MyTable;

And it will return the create table statement for the specificed table. This is useful if you have a table already created, and want to create the same table on another database.

Is it possible to get the insert statement for an already existing row, or set of rows? Some tables have many columns, and it would be nice for me to be able to get an insert statement to transfer rows over to another database without having to write out the insert statement, or without exporting the data to CSV and then importing the same data into the other database.

Just to clarify, what I want is something that would work as follows:

SHOW INSERT Select * FROM MyTable WHERE ID = 10;

And have the following returned for me:

INSERT INTO MyTable(ID,Col1,Col2,Col3) VALUES (10,'hello world','some value','2010-10-20');

Answer 1

Since you copied the table with the SQL produced by SHOW CREATE TABLE MyTable, you could just do the following to load the data into the new table.

INSERT INTO dest_db.dest_table SELECT * FROM source_db.source_table;

If you really want the INSERT statements, then the only way that I know of is to use mysqldump http://dev.mysql.com/doc/refman/5.1/en/mysqldump.htm. You can give it options to just dump data for a specific table and even limit rows.

Answer 2

I use the program SQLYOG where I can make a select query, point at the results and choose export as sql. This gives me the insert statements.