This article is a popular re-post from my Blogger blog from November of 2011 but is still relevant today.
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.
Here is the code:
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
Hope this helps someone!