[Advanced] Dump Query Results to .SQL File, help.

[Pretzal]

I'm pretty confident this is possible. What I want to do:
Run a select query --> Send query results to .SQL file for later use

Currently, i can use

select * from `characters` into outfile "D:\Dump.sql"

Which *almost* does what I want it to do. Unfortunately, it fails to format the dump file in SQL, and thus rather than making its contents

INSERT INTO `characters` (column1, column2, ...) VALUES (row1, row2, ...);

It just makes its contents the values with tabs inbetween e.g.

4   1   2   10000    500   465   8460 4,41,85,485   Frizire    6

So my question is:
How can I do something similar to a database dump, except only for a specific query, where I can re-execute the query from the file at a later date?

[stoneharry]

Ermmm, I know SQLyog can do this but I have no idea what the actual command sequence is.

I'll see if I can get it out of SQLyog later by looking at the logged queries. 7

edit: nope, failed by only logging the selection and not the dumping.

The only thing that might help might be: http://gilfster.blogspot.com/2005/11...s-to-file.html

Or you could just get SQLyog or another program that can do this sort of thing in a GUI, lol.

[Pretzal]

Well I've had a look at that URL, and it gets me a little closer. Now I can get:
"0","5","Frizire","2 43 1 0 0 4 0","0","2");

Which is *almost* there. Now i'm just missing

 INSERT INTO `characters` VALUES (

at the start.

But that lines terminated by '"', is there an equivilant? E.g. Lines started by "Insert into `characters` values (". I realise I could almost do this with the lines terminated by, making it

lines terminated by ");\r\n INSERT INTO `Characters` VALUES(

Which would work for all but the first query, which at worst could be added manually I suppose. Any ideas on the 'lines started by' or something similar command?

EDIT:
And I think Navicat pro has it, ill have a look into it...

[adaephon]

SELECT 'INSERT INTO '''characters''' (column1, column2, ...) VALUES (' + row1 + ', ' + CAST(row2 as VARCHAR) + ', ...)'
FROM characters

works in SQL Server

[Sonic Waffle]

mysqldump -u username -p –all-databases > dump.sql

or

mysqldump -u username -ppassword database_name > dump.sql