Re: How to prepare a bulk insert statements and execute them in a single query
Hello Rick,
Here I am providing you the SP. As you have mentioned there is no primary key in this table. Should I need to include? Please suggest.
DELIMITER $$
DROP PROCEDURE IF EXISTS `cswstats`.`sp_getstatsinstrepo`$$
CREATE DEFINER=`software`@`%` PROCEDURE `sp_getstatsinstrepo`(_strtdttm date,_code char(3))
BEGIN
-- call cswempdtls.sp_spcount('sp_getstatsinstrepo','cswstats');
insert into statsinstantrepo (d_date,c_code,c_jobid,i_operlevel,i_levelid,c_filename,c_subproj,c_acti,i_total,i_totalwtge,i_time,i_timewtge,t_enddttm) select _strtdttm as d_date,_code as c_code,tab1.c_jobid,tab1.i_operlevel,tab1.i_levelid,tab1.c_filename,tab1.c_subproj,tab1.c_acti,tab1.i_total,fn_getwtge_rec(tab1.i_total,tab1.c_acti,tab1.c_jobid,tab1.i_operlevel,tab1.i_levelid,tab1.c_subproj,tab1.i_time,tab1.cntycod) as i_totwtge,tab1.i_time/60 as i_time,fn_getwtge_time(tab1.i_total,tab1.c_acti,tab1.c_jobid,tab1.i_operlevel,tab1.i_levelid,tab1.c_subproj,tab1.i_time,tab1.cntycod)/60 as i_timewtge,tab1.`end time` from (select a.c_jobid,IF(c.i_saplevel=0,a.i_operlevel,c.i_saplevel) i_operlevel,a.i_levelid,left(a.c_filename,5)'cntycod',a.c_filename,d.c_subproj,b.c_acti,sum(b.i_total) as i_total,sum(b.i_time) as i_time,max(a.t_enddttm)'END TIME' from prod_mst a,prod_dtl b,cswempdtls.oper_nams c,joblkup_wtge d,actilkup_wtge e where a.i_autoid=b.i_prodsrl and c.c_deleted<>'Y' and a.c_code=c.c_code and if(a.c_shiftsap='ASS NGHT',(a.t_strtdttm between concat(date_add(_strtdttm,interval 1 day),' 00:00:00') and concat(date_add(_strtdttm,interval 1 day),' 06:30:00') or (a.t_strtdttm between convert(concat(_strtdttm,' 06:30:00'),datetime) and convert(concat(_strtdttm,' 23:59:59'),datetime))),date(a.t_strtdttm)=_strtdttm) and a.c_code=_code and a.c_jobid=d.c_jobid and d.c_jobid=e.c_jobid and b.c_acti=e.c_acti group by a.c_jobid,a.i_operlevel,a.i_levelid,left(a.c_filename,4),a.c_filename,b.c_acti) tab1;
END$$
DELIMITER ;