Mysql Store Procedure

Use curson and print output in store procedure

DECLARE _output TEXT DEFAULT '';
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
  FETCH cur1 INTO _ID;
  IF NOT done THEN
    SET _output = CONCAT(",", _ID); /*SEE ALT BELOW*/

  END IF;
UNTIL done END REPEAT;
CLOSE cur1;

SELECT _output;

Loop in store procedures
http://www.mysqltutorial.org/stored-procedures-loop.aspx 

Create CSV table

Create CSV table in mysql.

Create database CSV (or you can use any existing DB)

CREATE DATABASE CSV;

USE CSV;

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec) 

INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec

Records: 2 Duplicates: 0 Warnings: 0

SELECT * FROM test;

When you check table DB structure you will see files:

-rw-rw---- 1 mysql mysql 65 Oct 2 13:58 db.opt
-rw-rw---- 1 mysql mysql 8578 Oct 3 11:35 test.frm
-rw-rw---- 1 mysql mysql 35 Oct 3 11:36 test.CSM
-rw-rw---- 1 mysql mysql 60 Oct 3 11:37 test.CSV

test.CSV is a data file.

Now you can replace this file with prepared file with data. 

After upload new file you need execute 

FLUSH TABLE test

to refresh data in table.

CSV table not allow NULL value, you will need replace all null values in csv file to empty string "" or 0 for numeric values.

I used mysqldump to export table from one DB and import it to second DB. null value are display as "\N"
To change "\N" to empty string I used following command:

sed ':a;N;$!ba;s/\\N/""/g' p_trx_tickets.txt > p_trx_tickets.CSV

 After update data file execute:

FLUSH TABLE p_trx_tickets

Now data are available to do select and insert it to other DB.

When I did't replaced "\N" to "" (empty string) then I was receiving message "Table corrupted, need repair"