Delimiter //
/**
    popule la table loader info
*/
Drop Procedure If exists LOADER_POPULATE_LOADER_INFO //
Create Procedure LOADER_POPULATE_LOADER_INFO (
    In batchCount Int, 
    In tempTableName varchar(70), 
    In fieldForName varchar(70), 
    In fieldForValue varchar(70)
)
Begin
    Declare batchNum int default 1;
    main: begin

        -- clear load info
        truncate table LOADER_INFO;

        -- creation d'une table temporarraire pour faciliter la recopi des données 
        Drop temporary table If exists TMP_POPULATE ;

        Create Temporary Table TMP_POPULATE ( 
                TMP_POPULATE_BATCH_NUM Int Not Null Primary key auto_increment, 
                TMP_POPULATE_BATCH_VAL int
        ) ;
--         
--         -- interstion des numero de lots
        Insert Into TMP_POPULATE ( TMP_POPULATE_BATCH_VAL) Values (1);
        batchloop: loop
            Set @val = (Select max(TMP_POPULATE_BATCH_NUM) From TMP_POPULATE );

            if @val > batchCount Then
                leave batchloop;
            End if;
            Drop Temporary Table if Exists TMP_POPULATE2;
            Create temporary Table TMP_POPULATE2 as 
                Select TMP_POPULATE_BATCH_NUM + @val TMP_POPULATE_BATCH_VAL  From TMP_POPULATE ;
            Insert Into TMP_POPULATE ( TMP_POPULATE_BATCH_NUM, TMP_POPULATE_BATCH_VAL) 
                    Select TMP_POPULATE_BATCH_VAL , TMP_POPULATE_BATCH_VAL  From TMP_POPULATE2 ;
        end loop;
        
--         set @tempTable = tempTable;
--         if tempTable Not RegExp '^[ ]?`?[a-zA-Z1-9_]`?[ ]?$' Then
--             tempTable
--         End If ;
        -- purge des donnée en trop
        Delete From TMP_POPULATE  Where TMP_POPULATE_BATCH_NUM > batchCount;


        --  insertioon des données
        Set @queryTmp = Concat(
            'Insert Into LOADER_INFO (', 
                'LOADER_INFO_BATCH_NUM, LOADER_INFO_NAME, LOADER_INFO_VALUE', 
            ') Select ', 
                'TP.TMP_POPULATE_BATCH_NUM, TD.', fieldForName, 
                ', TD.',  fieldForValue, ' '
            ' From TMP_POPULATE TP Cross Join ', tempTableName, ' TD'
        );

        PREPARE stmt FROM @queryTmp;
        execute stmt;
        Deallocate prepare stmt;

        Drop Temporary table If exists TMP_POPULATE ;
        Drop Temporary Table if Exists TMP_POPULATE2;

    end ;
End //
Delimiter ;
