This is a re-post of one of my most popular blog post from my old Blogger blog.
I had a challenge yesterday that I would normally solve using some Visual Basic code but had to do it in pure SQL on a SQL 2005 server.
Loop through all the records in a table and then update the table based on some logic or condition.
Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.
Example Code: Update table with SQL Cursor and While Loop
DECLARE @myEmpID int DECLARE MyCursor CURSOR FOR SELECT DISTINCT EmployeeID FROM Employees WHERE Company=64 OPEN myCursor --Read the initial emploee id value from the cursor FETCH NEXT FROM myCursor INTO @myEmpID WHILE @@FETCH_STATUS = 0 BEGIN --Update goes here UPDATE Employees SET [Status] = 'T' WHERE EmployeeID = @myEmpID AND Company=54 FETCH NEXT FROM MyCursor INTO @myEmpID END CLOSE MyCursor