This is a very useful trick, when I have a list of things, and I want to update or populate a table according to that list. I import that list into the database, so it can be worked on, just like any other table. And then, I use Loops to iterate through the list, and do stuff on for each row in the list. Of course, the list has to have a unique counting key to step the iteration.

 

A Single Loop

 

Declare @MaxTempID Int

Select @MaxTempID = Max(TempID)

From TempEmployeesWithCompanies

–Select @MaxTempID

 

Declare @TempID Int — Counter

Set @TempID = 1

 

Declare @EmployeeID Int

Declare @CompanyID Int

 

While @TempID < @MaxTempID + 1

Begin

 

Select @EmployeeID = EmployeeID

from TempEmployeesWithCompanies

Where TempID = @TempID

 

Select @CompanyID = CompanyID

from TempEmployeesWithCompanies

Where TempID = @TempID

 

Update EmployeeTable

Set EmployeeTable.CompanyID = @CompanyID

Where tbEmployee.EmployeeID = @EmployeeID

 

Set @TempID = @TempID + 1

 

End

Go

 

 

A Double Loop

 

Declare @OuterCounter Int

Declare @InnerCounter Int

Set @OuterCounter = 1 — Initialize Outer Counter.

Set @InnerCounter = 1 — Initialize Inner Counter.

 

While @OuterCounter <= 2 — Number of iterations of Outer Loop.

Begin

Print ‘** Outer Loop **’

 

While @InnerCounter <= 5 — Number of interations in Inner Loop.

Begin

Print ‘Inner Loop’

Set @InnerCounter = @InnerCounter + 1

End

 

Set @InnerCounter = 1 — Reset Inner Loop to 1 when it gets to the end.

Set @OuterCounter = @OuterCounter + 1

End

Advertisements