It takes 30 seconds with the cursor, and the use of temporary tables will only take less than one second, and it is a modified storage process!
Try not to use the cursor, although there is only a cursor processing, try not to use the trigger, it may cause unpredictable results, if you use, do your test, to ensure that there is no all the process is controllable!
Create Procedure SP_THCHL
@Result Numeric Output
AS
Declare @lsh as numeric
SELECT @ LSH = max (liush) from TB_MAINTAB
IF @lsh is null
Set @ LSH = 1
Else
Set @ LSH = @ LSH 1
DELETE from TB_MAINTEMP
INSERT INTO TB_MAINTEMP (Matnr, Vbeln, Kriqi) (Select Material, DLVNO, DLVTIME FROM TBLDLVINFOBACK WHERE SERIAL IN (SELECT Serial from TB_SERIAL))
INSERT INTO TB_MAINTAB (Matnr, Vbeln, Kriqi, THJS, LIUSH) (Select Matnr, Vbeln, Convert (Varchar (10), Kriqi, 120), Count (Matnr), @ LSH from TB_MAINTEMP GROUP BY MATNR, VBELN, Convert (Varchar (10), Kriqi, 120))))
SELECT @ result = @ LSH
Return @Result
Go
