Mysql – Get Insert Statement for existing row in MySQL

MySQLsyntax

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');

Best Answer

There doesn't seem to be a way to get the INSERT statements from the MySQL console, but you can get them using mysqldump like Rob suggested. Specify -t to omit table creation.

mysqldump -t -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10"
Related Topic