Hi Kristen,
thanks,
It has solve.But i have another problem ,which i am discussng below.
Here i am getting performance issue using cursor in above query.I want to update row by row record with genearting dynamic id for each row .Can you help me how can i overcome this performance issue or any alternate for this which provide me row by row updation .I am giving procedure which i am using.
ALTER procedure [dbo].[updateNapsRecon]
as
DECLARE @irisRet_Ref_No varchar(50),
@glRet_Ref_No varchar(50),
@irisRecon_id varchar(50),
@glRecon_id varchar(50),
@iris_sid varchar(50),
@gl_sid varchar(50),
@iris_amt varchar(50),
@gl_amt varchar(50),
@genrateRecon_id varchar(50),
@genrateSid varchar(50),
@qcbRet_Ref_No varchar(50),
@qcb_sid varchar(50),
@qcb_amt varchar(50),
@qcb_recon_id varchar(50),
@irisversion int,
@glversion int,
@qcbversion int,
@ErrorMessage varchar(2000)
,@ErrorSeverity tinyint
,@ErrorState tinyint,
@count int=1
-- Here i create cursor name is c3 according to require condition
DECLARE c4 CURSOR OPTIMISTIC FOR SELECT i.sid,i.RETR_REF_NO ,g.sid,g.RETR_REF_NO ,q.sid,q.RETR_REF_NO
, i.version,g.version,q.version FROM IRIS_stg i inner join Gl_1015_stg g
on i.RETR_REF_NO=g.RETR_REF_NO inner join QCB_STG q on
q.RETR_REF_NO=i.RETR_REF_NO where
q.TRANS_AMOUNT=i.AMT_TRAN and
i.AMT_TRAN=
g.TRA_AMT and i.PAN=q.CARD_NUMBER
and
i.SYS_TRACE_AUDIT_NO=g.stan
and i.DATE_LOC_TRAN=g.TRA_DATE
and g.AUTHORIZING_CHANNEL IN('9999')
AND g.PROC_CODE=01 AND g.ACQUIRING_CHANNEL_ID=28
AND g.TELL_ID=9953 AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')
and
i.AUTHORIZER IN('9999')
AND i.ACQUIRING_CHANNEL_ID=28 AND i.RESP_CODE IN('000','036','037') AND i.PROC_CODE_FIRST_2=01
AND i.BIN IN(434141,484823,428246,472839)
AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')
-- open the cursor
OPEN c4;
-- fetch one row column value and store in to @colName,@colType
FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@gl_sid,@glRet_Ref_No,
@qcb_sid,@qcbRet_Ref_No,
@irisversion,@glversion,@qcbversion;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--here i am inserting record in diffrent table on basis of sid
insert into IRIS_AUDIT select * from IRIS_stg where sid=@iris_sid;
insert into GL_1015_AUDIT select * from GL_1015_STG where sid=@gl_sid;
insert into QCB_AUDIT select * from QCB_STG where sid=@qcb_sid;
--here i am genearting recon id dynamically basis of retr_ref_no and sid
set @genrateRecon_id=@irisRet_Ref_No+@iris_sid;
set @irisversion=@irisversion+1;
set @glversion=@glversion+1;
set @qcbversion=@qcbversion+1;
--updating row by row record basis on sid
update IRIS_STG set RECON_ID=@genrateRecon_id ,RECON_STATUS='AM' ,VERSION=@irisversion where sid=@iris_sid
update GL_1015_STG set RECON_ID=@genrateRecon_id ,RECON_STATUS='AM' ,VERSION=@glversion where sid=@gl_sid
update QCB_STG set RECON_ID=@genrateRecon_id ,RECON_STATUS='AM' ,VERSION=@qcbversion where sid=@qcb_sid
print @count
set @count=@count+1
-- fetch one row column value and store in to @colName,@colType inside loop
FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@gl_sid,@glRet_Ref_No,
@qcb_sid,@qcbRet_Ref_No,
@irisversion,@glversion,@qcbversion;
END
--close while loo body
-- close the cursor c3
CLOSE c4;
--DEALLOCATE cursor c3 that mean every execution time it will treate as new one
DEALLOCATE c4;
In above procedure I am creating cursor and fetching one row and creating reconid for that row and update that row`s recon id null to new generated recon id.In this process i have millions of record and this process take more time .I want to reduce time can u help me.
thanks & regards,
shivam