How to replace a cursor

[aalmeida]

The following cursor is in use because the source data may have more then one record to the same key (CustID, CampID, Office) and we are interested only on the last one order by the ChangeDateTime a datetime type. it is working fine but there is a need to optmize it to run faster.

Declare cr_Cust_Test Cursor FOR
SELECT DISTINCT CustID, CampID, Office
FROM #CustUpdate
FOR Read Only

OPEN cr_Cust_Test
FETCH NEXT FROM cr_Cust_Test INTO @CustID, @CampaignID, @Office

WHILE @@Fetch_Status <> -1
BEGIN
INSERT INTO Customer (CustID, CampaignID, Office, LastName, FirstName, MiddleInit, Address1, Address2, SSN, City, State, HomePhone, Zip, ResidCD, ResidDate, EmployerName, JobTitle, EmploymentDate, WorkPhone, OtherPhone, BTTC, BTTCCode, EmailAddress, CustType)
SELECT CustID, CampID, Office, LastName, FirstName, MiddleInit, Address1, Address2, SSN, City, State, HomePhone, Zip, ResidCD, ResidDate, EmployerName, JobTitle, EmploymentDate, WorkPhone, OtherPhone, BTTC, BTTCCode, EmailAddress, CustType
FROM #CustUpdate
WHERE CustID = @CustID
AND CampID = @CampaignID
AND Office = @Office
AND ChangeDateTime = (SELECT MAX (ChangeDateTime) FROM #CustUpdate
WHERE CustID = @CustID
and CampID = @CampaignID
and Office = @Office)
FETCH NEXT FROM cr_Cust_Test INTO @CustID, @CampaignID, @Office
END

CLOSE cr_Cust_Test
DEALLOCATE cr_Cust_Test

thanks AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;

 

[balves]

There's no need for cursors here. Think sets, not cursors.

Your FROM clause can be two tables:

INSERT ...
SELECT ...
FROM #CustUpdate A
INNER JOIN
(SELECT CustID, CampID, Office, Max(ChangeDateTime) AS MaxChangeDateTime
FROM #CustUpdate
GROUP BY CustID, CampID, Office) B
ON A.CustID = B.CustID
AND A.CampID = B.CampID
AND A.Office = B.Office