Get Insert Statement for existing row in MySQL

mysql syntax
Kibbee picture Kibbee · Oct 20, 2010 · Viewed 150k times · Source

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

Kaivosukeltaja picture Kaivosukeltaja · Dec 7, 2012

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 questions

How do I modify a MySQL column to allow NULL?

MySQL 5.0.45 What is the syntax to alter a table to allow a column to be null, alternately what's wrong with this: ALTER mytable MODIFY mycolumn varchar(255) null; I interpreted the manual as just run the above and it would recreate …

Read More
How can I fix MySQL error #1064?

When issuing a command to MySQL, I'm getting error #1064 "syntax error". What does it mean? How can I fix it?

Read More
What's the difference between comma separated joins and join on syntax in MySQL?

For example if I were to have a table "Person" with a column "id" that references a column "id" in table "Worker" What would the difference between these two queries be? They yield the same results. SELECT * FROM Person JOIN …

Read More