How to append data from SQL to an existing file

Question

SQL has the option to dump data into a file, using the INTO OUTFILE option, for exmaple

SELECT * from FIshReport INTO OUTFILE './FishyFile'

The problem is, this command is only allowed if the file didn't exist before it. It creates the file and then enters the data. So, is there any way to append data to a file this way?

Answer 1

As the MySQL page on SELECT syntax suggests:

http://dev.mysql.com/doc/refman/5.0/en/select.html

the alternative to this is to issue the SELECT from the MySQL client:

However, if the MySQL client software is installed on the remote machine,
you can instead use a client command such as mysql -e "SELECT ..." > file_name 
to generate the file on the client host. 

which, in your case, would be modified to be:

mysql -e "SELECT * from FishReport" >> file_name

so that you simply append to the file.

From your Tcl script, you could simply issue this as an exec command:

http://www.tcl.tk/man/tcl/tutorial/Tcl26.html

Answer 2

I think MySQL does not allow appending data to an existing file or overwriting an existing file for security reasons. A work around could be to save resuts in seperate files and then append the using file IO.