Export MySQL Data as Insert Statements

Question

Hi All,

I'm working in Ubuntu with MySql and I also have Query Browser and Administrator installed, I'm not afraid of the command line either if it helps.

I want simply to be able to run a query and see a result set but then convert that result set into a series of commands that could be used to create the same rows in a table of an identical schema.

I hope the question makes sense, it's quite a simple problem and one that must have been solved but I can't for the life of me work out where this kind of conversion is made available.

Thanks in advance,

Gav

Answer 1

I do not know if I understood you at all but you can use a SELECT INTO statement.

SELECT *
INTO new_table_name
FROM old_tablename
WHERE ...

Answer 2

I think you need to use a command line utility mysqldump http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html if you want to dump one or more tables.
If you need to dump a result of an arbitrary query and restore it later, take a look on SELECT ... INTO OUTFILE and LOAD DATA INFILE( http://dev.mysql.com/doc/refman/5.0/en/load-data.html)