CODEWITHSUNDEEP
mysqlsqlfile-ioappendinto-outfile
n00b programmer
n00b programmer

Reputation: 2701

How to append data from SQL to an existing file

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?

Upvotes: 7

Views: 23493

Answers (4)

abbas
abbas

Reputation: 1

You could just add it to a variable. Then use a 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;

Upvotes: 0

Tasawer Khan
Tasawer Khan

Reputation: 6148

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.

Upvotes: 1

kvista
kvista

Reputation: 5059

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

Upvotes: 4

John Pickup
John Pickup

Reputation: 5115

You could always append the output from your SQL script to a file using >>

For example (for Sybase):

isql < script.sql >> outputfile.out

I can't tell you what the equivalent is for MySQL but the principle should be the same.

Of course output will all go to one file so if your SQL script is outputting various SQL selects to different output files then you'd need to split the script up.

Upvotes: 0

Related Questions