Aiden Aiden - 1 month ago 14
SQL Question

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

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

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'