How to add data from SQL to an existing file

Question

SQL has the ability to upload data to a file using the INTO OUTFILE option for exmaple

SELECT * from FIshReport INTO OUTFILE './FishyFile' 

The problem is that this command is only allowed if the file did not exist before. It creates a file and then enters the data. So, is there a way to add data to a file this way?

Answer 1

As the MySQL page shows in the SELECT syntax:

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

an alternative to this is issuing 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 will be changed like this:

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

so that you simply add to the file.

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

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

Answer 2

I think MySQL does not allow adding data to an existing file or overwriting an existing file for security reasons. The job may be to save the resulting files in separate files, and then add the file using the IO file.

Answer 3

You can always add output from your SQL script to a file using >>

For example (for Sybase):

 isql < script.sql >> outputfile.out 

I can’t tell you that the equivalent is for MySQL, but the principle should be the same.

Of course, the output will go to a single file, so if your SQL script outputs various SQL samples to different output files, you need to split the script up.

Answer 4

You can simply add it to a variable. Then use SELECT with UNION.

 declare t varchar(100); set @myvar = concat(' select * INTO OUTFILE \'',file,'\' from ( select \'',t,'\' union all SELECT col from tbl where x ) a' ); PREPARE stmt1 FROM @myvar; EXECUTE stmt1; Deallocate prepare stmt1;