September 10, 2008 at 7:19 am
I'm looking for a solution to replacing a cursor in a stored procedure that is used in a loop to process 20k to 1mm records every day. The cursor selects all records from a staging table and then loops thru them checking the date of the record. It will then check the history table for existence of the record and will update the values, else insert the new record. I attached the top part of the sproc so you get the idea of what's going on now. Any help or examples are appreciated.
declare CalcHistory CURSOR FOR
SELECT ID, AudType, AudCompID, AudDate, AudTime, cast(AudHomeid as decimal(15,0)) as AudHomeid, AudName, AudMortgageReferenceNumber, AudGMS_Tracking, AudAmount,
AudCheckNumber, AudReturnReasonCode, AudReturnEffectiveDate, AudRemarks, ClearedDate
FROM dailyupload
OPEN CalcHistory
Fetch Next from CalcHistory into
@ID, @AudType, @AudCompID,@AudDate, @AudTIme, @AudHomeid, @AudName ,
@AudMortgageReferenceNumber, @AudGMS_Tracking, @AudAmount, @AudCheckNumber,
@AudReturnReasonCode, @AudReturnEffectiveDate, @AudRemarks, @ClearedDate
WHILE @@FETCH_STATUS = 0
BEGIN
if left(@auddate,4) = '1999'
begin
set @count = (select count(*) from [hist1999] where id = @ID)
if @count > 0
begin
update hist1999
set Audtype = @Audtype,
Audcompid = @Audcompid,
Auddate = @Auddate,
AudTime = @AudTime,
Audhomeid = @Audhomeid,
Audname = @Audname,
AudMortgageReferenceNumber = @AudMortgageReferenceNumber,
AudGMS_tracking = @AudGMS_tracking,
AudAmount = @AudAmount,
AudCheckNumber = @AudCheckNumber,
AudReturnreasoncode = @AudReturnreasoncode,
AudReturnEffectiveDate = @AudReturnEffectiveDate,
AudRemarks = @AudRemarks,
ClearedDate = @ClearedDate
where id = @id
end
else
begin
insert hist1999
(ID,Audtype,Audcompid,Auddate,AudTime,Audhomeid,Audname, AudMortgageReferenceNumber,AudGMS_tracking,AudAmount, AudCheckNumber,AudReturnreasoncode,AudReturnEffectiveDate, AudRemarks,ClearedDate)
values (@ID, @Audtype,@Audcompid,@Auddate,@AudTime,@Audhomeid,@Audname,@AudMortgageReferenceNumber,@AudGMS_tracking, @AudAmount,@AudCheckNumber,@AudReturnreasoncode,@AudReturnEffectiveDate,@AudRemarks, @ClearedDate)
end
end
Open in New Window Select All Tags:
microsoft, sql server, 2005
September 10, 2008 at 10:27 am
Update A
set a.AudType = b.AudType,
a.AudCompID = b.AuDCompID,
a.AudDate = b.AudDate,
xxx
xxx
xxx
a.ClearedDate = b.ClearedDate
from hist1999 as a
JOIN dailyupload as b
on a.ID = b.ID
Insert into hist1999
(ID, AudType, AudCompID, AudDate, AudTime, cast(AudHomeid as decimal(15,0)) as AudHomeid, AudName, AudMortgageReferenceNumber, AudGMS_Tracking, AudAmount,
AudCheckNumber, AudReturnReasonCode, AudReturnEffectiveDate, AudRemarks, ClearedDate)
Select ID, AudType, AudCompID, AudDate, AudTime, cast(AudHomeid as decimal(15,0)) as AudHomeid, AudName, AudMortgageReferenceNumber, AudGMS_Tracking, AudAmount,
AudCheckNumber, AudReturnReasonCode, AudReturnEffectiveDate, AudRemarks, ClearedDate from dailyupload
where id not in (Select ID from hist1999)
The Insert part can be written in a better way. This is just to give you an idea of how you can do this without Cursors.
-Roy
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply