Insert & Update records (Posted by Stewart Chew) - www.foxite.com

Dear Expert,

I would like to inquiry to all the expertise. If i have insert a lot a records into a cursor after that i use the command below to insert all separate table. I have a problem is when insert or update records need to take a long time. Does anyone here help me to resolve this problem.

SELECT part_code, SUM(iss_qty) as tl_iss, SUM(rtn_qty) as tl_rtn;
	FROM cWrkOrdr;
	GROUP BY part_code;
	INTO CURSOR cSumTtlQty NOFILTER

** UPDATE RETURNED
INSERT INTO Wrk_Ordr (rtn_date, wrk_ordr, prd_ordr, rtn_qty, part_code, qty_req, to_loc, id_upd, dt_upd);
	SELECT rtn_date, wrk_ordr, prd_ordr, rtn_qty, part_code, qty_req, to_loc, id_upd, dt_upd;
	FROM cWrkOrdr;
	WHERE cWrkordr.rtn_qty > 0

INSERT INTO stk_detl (date, ref_no, odr_ref, part_code, part_desc, part_loc, rtn_qty, unit_cost, do_date, source, updated, post_dt);
	SELECT cWrkOrdr.rtn_date, cWrkOrdr.prd_ordr, cWrkOrdr.wrk_ordr, cWrkOrdr.part_code, stkmst.part_desc, cWrkOrdr.to_loc,;
		cWrkOrdr.rtn_qty, stkmst.dec_cost2, cWrkOrdr.rtn_date as do_date, 'RF', .T., DATETIME();
		FROM cWrkOrdr INNER JOIN stkmst ON cWrkOrdr.part_code = stkmst.part_code;
		WHERE cWrkordr.rtn_qty > 0

UPDATE mrs_dtl SET tl_rtn = cWrkOrdr.rtn_qty;
	FROM mrs_dtl;
	INNER JOIN cWrkOrdr ON mrs_dtl.mrs_no = cWrkOrdr.mrs_no;
	WHERE cWrkOrdr.part_code = mrs_dtl.son_code;
	AND cWrkOrdr.prd_ordr = mrs_dtl.prd_ordr;
	AND cWrkordr.rtn_qty > 0
			
UPDATE stkmst SET qty_hand = stkmst.qty_hand + cSumTtlQty.tl_rtn;
	FROM stkmst;
	INNER JOIN cSumTtlQty ON stkmst.part_code = cSumTtlQty.part_code;
	WHERE cSumTtlQty.tl_rtn > 0

UPDATE loc_qty SET qty = loc_qty.qty + cSumTtlQty.tl_rtn;
	FROM loc_qty;
	INNER JOIN cSumTtlQty ON loc_qty.part_code = cSumTtlQty.part_code;
	WHERE loc_qty.part_loc = 'STORE';
	AND cSumTtlQty.tl_rtn > 0

UPDATE prd_detl SET tl_rtn = cWrkOrdr.rtn_qty;
	FROM cWrkOrdr;
	INNER JOIN prd_detl ON cWrkOrdr.prd_ordr = prd_detl.prd_ordr;
	WHERE cWrkOrdr.part_code = prd_detl.part_code;
	AND cWrkordr.rtn_qty > 0

UPDATE mrs_hdr SET status = 'R' FROM cWrkOrdr INNER JOIN mrs_hdr ON cWrkOrdr.mrs_no = mrs_hdr.mrs_no

** UPDATE ISSUED
INSERT INTO Wrk_Ordr (date, wrk_ordr, prd_ordr, iss_qty, part_code, qty_req, frm_loc, id_upd, dt_upd);
	SELECT date, wrk_ordr, prd_ordr, iss_qty, part_code, qty_req, frm_loc, id_upd, dt_upd;
	FROM cWrkOrdr;
	WHERE cWrkordr.iss_qty > 0
			
INSERT INTO stk_detl (date, ref_no, odr_ref, part_code, part_desc, part_loc, qty, unit_cost, do_date, source, updated, post_dt);
	SELECT cWrkOrdr.date, cWrkOrdr.prd_ordr, cWrkOrdr.wrk_ordr, cWrkOrdr.part_code, stkmst.part_desc, cWrkOrdr.frm_loc,;
		cWrkOrdr.iss_qty, stkmst.dec_cost2, cWrkOrdr.date as do_date, 'IP', .T., DATETIME();
		FROM cWrkOrdr;
		INNER JOIN stkmst ON cWrkOrdr.part_code = stkmst.part_code;
		WHERE cWrkordr.iss_qty > 0

UPDATE mrs_dtl SET tl_iss = cWrkOrdr.iss_qty;
	FROM cWrkOrdr;
	INNER JOIN mrs_dtl ON cWrkOrdr.mrs_no = mrs_dtl.mrs_no;
	WHERE cWrkOrdr.part_code = mrs_dtl.son_code;
	AND cWrkOrdr.prd_ordr = mrs_dtl.prd_ordr;
	AND cWrkordr.iss_qty > 0
			
UPDATE stkmst SET qty_hand = qty_hand - cSumTtlQty.tl_iss;
	FROM stkmst;
	INNER JOIN cSumTtlQty ON stkmst.part_code = cSumTtlQty.part_code

UPDATE loc_qty SET qty = qty - cSumTtlQty.tl_iss;
	FROM loc_qty;
	INNER JOIN cSumTtlQty ON loc_qty.part_code = cSumTtlQty.part_code;
	WHERE loc_qty.part_loc = 'STORE';
	AND cSumTtlQty.tl_iss > 0

UPDATE prd_detl SET tl_iss = cWrkOrdr.iss_qty;
	FROM cWrkOrdr;
	INNER JOIN prd_detl ON cWrkOrdr.prd_ordr = prd_detl.prd_ordr;
	WHERE cWrkOrdr.part_code = prd_detl.part_code;
	AND cWrkordr.iss_qty > 0

SELECT cWrkOrdr
ZAP IN SELECT([cWrkOrdr])	

IF USED([cSumTtlQty])
  SELECT cSumTtlQty
  USE
ENDIF

THISFORM.Release