I am using MySQL benchmark. I have to create a table and write a procedure to read 10 from textfilnd load them to the created table.
I used the query:
use test;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_table`()
BEGIN
LOAD DATA LOCAL INFILE "D:/tablevalue.txt"
INTO TABLE new_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
END $$
DELIMITER ;
But error like load data is not allowed in stored procedure, and then I used the query.
use test;
drop procedure if exists `proctable`;
DELIMITER $$
create definer =`root`@`localhost` procedure `proctable`()
begin
do sys_exec(concat('perl LOAD_DATA_INFILE.pl test.new_table ', 'D:\tablevalue.txt'));
end $$
DELIMITER ;
But data is not uploading from the file to the table.
If is there any other option let me know
There are some restrictions on stored procedures. For example - it is not possible to use LOAD DATA INFILE command. Try to do it without procedures, just call your LOAD DATA INFILE statement as alone command.