HVDataInsert

BEGIN

​

DECLARE table_name varchar(50) DEFAULT '';

DECLARE columns_list varchar(2000) DEFAULT '';

DECLARE values_list text DEFAULT '';

DECLARE cur_position INT DEFAULT 1;

DECLARE cur_string VARCHAR(2000);

DECLARE cur_table varchar(50) DEFAULT '';

DECLARE cur_column varchar(50);

DECLARE cur_value VARCHAR(2000);

DECLARE delimiter_length TINYINT UNSIGNED;

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

​

START TRANSACTION;

​

​

SET cur_position = 1;

​

​

SET delimiter_length = CHAR_LENGTH('|');

​

​

​

WHILE CHAR_LENGTH(incoming) > 0 AND cur_position > 0 DO

SET cur_position = INSTR(incoming, '|');

​

    IF cur_position = 0 THEN

    SET cur_string = incoming;

    ELSE

    SET cur_string = LEFT(incoming, cur_position - 1);

    END IF;

​

​

​

​

    IF TRIM(cur_string) != '' THEN

    SET cur_table = SUBSTRING_INDEX(cur_string, '.', 1);

    SET cur_column = SUBSTRING_INDEX(SUBSTRING_INDEX(cur_string, '.', (LENGTH(REPLACE(cur_string, '.', '')) -LENGTH(cur_string))), ':', 1);

    SET cur_value = CONCAT(''', SUBSTRING_INDEX(cur_string, ':', (LENGTH(REPLACE(cur_string, ':', '')) - LENGTH(cur_string))), ''');

    If table_name <> cur_table and table_name <> '' THEN

        SELECT CONCAT('REPLACE INTO ', table_name, '(', columns_list, ') VALUES (',  values_list, ');') into @executestatement;

        PREPARE stmtinsert FROM @executestatement;

        EXECUTE stmtinsert;

        SET @executestatement = '';

        SET columns_list = '';

        SET values_list = '';

    END IF;

    SET table_name = cur_table;

    IF columns_list = '' THEN 

        SET columns_list = CONCAT(columns_list, cur_column);

    ELSE

        SET columns_list = CONCAT(columns_list, ',', cur_column);

    END IF;

    IF values_list = '' THEN

        SET values_list = CONCAT(values_list, cur_value);

    ELSE

        SET values_list = CONCAT(values_list, ',', cur_value);

    END IF;

    END IF;

​

SET incoming = SUBSTRING(incoming, cur_position + delimiter_length);

​

​

END WHILE;

​

​

SELECT CONCAT('REPLACE INTO ', table_name, '(', columns_list, ') VALUES (',  values_list, ');') into @executestatement;

PREPARE stmtinsertfinal FROM @executestatement;

EXECUTE stmtinsertfinal;

SET @executestatement = '';

​

COMMIT;

​

END