CODEWITHSUNDEEP
mysql
Richard C
Richard C

Reputation: 411

MySQL OutFile to use data from table

I'm trying to generate an export that runs monthly which appends a value from one of the columns within a table to the OutFile name, is this even possible?

The table is mytable and the column is col1 which contains "201601" at the minute

I would like my OutFile to show "/tmp/Output_201601" where the 201601 on next run will have automatically changed to 201602 etc

Upvotes: 0

Views: 49

Answers (1)

Marcin C.
Marcin C.

Reputation: 169

You can try to do this with stored procedure. Eg. when you call this procedure - it export data from table mytable to file with name D:/tmp/Output_ + top value from field Col1.

CREATE PROCEDURE `PrepareReport`()
BEGIN
 SET @OutputFileName := 'D://tmp//Output_';

 SELECT Col1 INTO @ReportNr FROM mytable ORDER BY Col1 DESC LIMIT 1;

 SET @q1 := concat("SELECT * FROM mytable INTO OUTFILE '", @OutputFileName, @ReportNr, ".csv'");
 PREPARE s1 FROM @q1;
 EXECUTE s1;
 DEALLOCATE PREPARE s1;
END

Upvotes: 1

Related Questions