CODEWITHSUNDEEP
mysqlloopsstored-proceduresinto-outfile
Chris
Chris

Reputation: 87

MySQL loop statement over column with INTO OUTFILE unique name

I have a database with few tables, but we are really interested in two. In the first one I have data with id's assigned to it. In the second I have chosen id's. What I'm trying to do is to run in a loop a statement that will select what I'm interested in from the first table, based on the condition provided to the statement. Now I want to loop the statement so in each call the condition will be the id from second table. Moreover I want the result to be saved to the file with name corresponding to the id that was called on. So far I have this:

DELIMITER $$

CREATE PROCEDURE generateData(
    IN idNumber INT
)

BEGIN
    SELECT 
        id, 
        name, 
        d_year, 
        d_month,
        d_day,
        d_value   
    FROM d_dataframe 
    WHERE
        id = idNumber
    INTO OUTFILE "D:\\test.csv"
    CHARACTER SET utf-8
    FIELDS TERMINATED BY ';'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';
    
END $$

DELIMITER ;

in the above code I need a way to replace INTO OUTFILE "D:\test.csv" part, so when calling on the second table for values, let's say 101, 102, 103, to get files 101.csv, 102.csv, 103.csv. What I need to add and how to call that loop?

Thanks in advance!

Chris

Upvotes: 1

Views: 282

Answers (1)

nbk
nbk

Reputation: 49410

as Akina said use prepared statements

DELIMITER $$

CREATE PROCEDURE generateData(
    IN idNumber INT
)

BEGIN
SET @sql  = CONCAT('
    SELECT 
        id, 
        name, 
        d_year, 
        d_month,
        d_day,
        d_value   
    FROM d_dataframe 
    WHERE
        id = ',idNumber,'
    INTO OUTFILE "D:\\test',idNumber,'.csv"
    CHARACTER SET utf8
    FIELDS TERMINATED BY \';\'
    ENCLOSED BY \'\\"\'
    LINES TERMINATED BY \'\\r\\n\';');
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END $$

DELIMITER ;

Upvotes: 1

Related Questions