How to convert this sql server cursor to set-based for speed?

Question

I have to insert into multiple tables if the condition is true in one table i.e.

Table

Person

tableID      PersonUniqueNumber
1               123
2               1234
3               121
4               12
5               113333

and another table

RentedHousesDetail

HouseId(tableId)      HouseName         HouseLocation    ISOK
   1                     A                  CA            NO
   2                     B                  DT            NULL
   3                     C                  NY            NULL
   4                     D                  CA        
   5                     E                  CA

and other tables

Table CALIFORNIAHOUSE

Table STATUSGREEN

So, what I have to do is to for EACH person, I have to see if his houselocation in RentedHousesDetail is CA then I have to do single row insertion of RentedHousesDetail.ID in table CALIFORNIAHOUSE and STATUSGREEN and update RentedHousesDetail.ISOK column to NO.

There are thousands of rows in the table, so I wrote a cursor e.g.

DECLARE variables

DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

select PERSON.ID of those rows only where we have CA in RentedhouseDetails

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName
INTO variables

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM cursorName

   FOR EACH ROW that we have from cursor, insert into CALIFORNIAHOUSE and STATUSGREEN and update RentedHousesDetail.ISOK to NO

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursor

Please tell me is it ok to use cursor on thousands of rows in Person and Rentedhousedetails table? How can I convert it into set based operation for speed?

Answer 1

I think there is no need to use cursor here . first you have to select PERSON.ID of those rows only where we have CA in RentedhouseDetails like

select p.id from Person p JOIN RentedHousesDetail r ON p.ID=r.ID 
where r.HouseLocation='CA'

then insert all that record into CALIFORNIAHOUSE and STATUSGREEN table

Like this

 Insert into CALIFORNIAHOUSE 
 select p.id from Person p JOIN RentedHousesDetail r ON p.ID=r.ID
 where r.HouseLocation='CA'

AND

Insert into STATUSGREEN 
select p.id from Person p JOIN RentedHousesDetail r ON p.ID=r.ID
where r.HouseLocation='CA'

AND Finally Update table RentedHousesDetail where HouseLocation='CA' as 'NO'

like this

update RentedHousesDetail set ISOK='NO' from Person p JOIN RentedHousesDetail r ON p.ID=r.ID
where r.HouseLocation='CA'