How to update incrementally the data into another table using loops?

Question

I have one query which is like this:

CREATE TABLE #Employee
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Basavaraj Biradar', 0),
        (2, 'Shree Biradar', 0),
        (3, 'Kalpana Biradar', 0)
GO

DECLARE @LoopCounter INT = 1, @MaxEmployeeId INT = 10 , 
        @EmployeeName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = (Name)
   FROM #Employee WHERE Id = @LoopCounter


   SET @LoopCounter  = @LoopCounter  + 1     
    PRINT @EmployeeName     
END

Giving Result:

Basavaraj Biradar
Shree Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar
Kalpana Biradar

Expected Output:

1 Basavaraj Biradar
2 Shree Biradar
3 Kalpana Biradar
4 Basavaraj Biradar
5 Shree Biradar
6 Kalpana Biradar
7 Basavaraj Biradar
8 Shree Biradar
9 Kalpana Biradar
10 Basavaraj Biradar
11 Shree Biradar
12 Kalpana Biradar

Answer 1

As there are 3 rows in the table, the SELECT statement runs only 3 times. After that, it just keeps printing the @EmployeeName variable until the loop breaks. Because the last record in the table has 'Kalpana Biradar' in EmployeeName column, it does not get changed in subsequent iterations hence the same string is printed every time.

To execute the loop for actual number of records in the table, the code can be modified to set @MaxEmployeeId as:

SELECT @MaxEmployeeId = COUNT(*) FROM #Employee

EDIT:

As you want to start from 1st record after reaching the end, the code should be like this:

DECLARE @LoopCounter INT = 1, @MaxEmployeeId INT = 10 , 
        @EmployeeName NVARCHAR(100), @AnotherCounter INT = 1

WHILE(@AnotherCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = (Name)
   FROM #Employee WHERE Id = @LoopCounter

   IF @LoopCounter >= (SELECT COUNT(*) FROM #Employee)
         SET @LoopCounter = 1
   ELSE
      SET @LoopCounter  = @LoopCounter  + 1 

    PRINT @EmployeeName
    SET @AnotherCounter = @AnotherCounter + 1     
END

DROP TABLE #employee

Answer 2

This can be done without a WHILE loop or a CURSOR. All you need is a Tally Table:

SQL Fiddle

DECLARE @MaxEmployeeId INT = 10;

DECLARE @N INT; 
SELECT @N = CEILING(@MaxEmployeeId/(COUNT(*)*1.0)) FROM #Employee;

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
CteTally(N) AS(
    SELECT TOP(@N) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteFinal AS(
    SELECT *, Rn = ROW_NUMBER() OVER(ORDER BY N, Id) 
    FROM #Employee e
    CROSS JOIN CteTally t
)
SELECT Rn, Name FROM CteFinal WHERE Rn <= @MaxEmployeeId ORDER BY Rn

---

If it's only the number of times the records should be repeated:

SQL Fiddle

DECLARE @RepeatTimes INT = 4
;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
CteTally(N) AS(
    SELECT TOP(@RepeatTimes) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT 
    Rn = ROW_NUMBER() OVER(ORDER BY N, Id), e.Name
FROM Employee e
CROSS JOIN CteTally t
ORDER BY Rn

Result:

| Rn |              Name |
|----|-------------------|
|  1 | Basavaraj Biradar |
|  2 |     Shree Biradar |
|  3 |   Kalpana Biradar |
|  4 | Basavaraj Biradar |
|  5 |     Shree Biradar |
|  6 |   Kalpana Biradar |
|  7 | Basavaraj Biradar |
|  8 |     Shree Biradar |
|  9 |   Kalpana Biradar |
| 10 | Basavaraj Biradar |
| 11 |     Shree Biradar |
| 12 |   Kalpana Biradar |

Answer 3

You will need two loops. The outer loop defines the number of repetitions, while the inner loop will print all available names from the table. Try this -

CREATE TABLE #Employee
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Basavaraj Biradar', 0),
        (2, 'Shree Biradar', 0),
        (3, 'Kalpana Biradar', 0)
GO

DECLARE @OuterLoopCounter INT = 1, 
        @InnerLoopCounter INT=1,
        @MaxEmployeeID INT,
        @EmployeeName nvarchar(100)

WHILE @OuterLoopCounter <= 4 -- Change to whatever value
BEGIN
   SELECT @MaxEmployeeID = MAX(ID) FROM #Employee
   WHILE @InnerLoopCounter <= @MaxEmployeeID
   BEGIN
        SELECT @EmployeeName = Name
        FROM #Employee WHERE Id = @InnerLoopCounter
        PRINT @EmployeeName 
        SET @InnerLoopCounter  = @InnerLoopCounter  + 1     
   END
   SET @InnerLoopCounter = 1
   SET @OuterLoopCounter =@OuterLoopCounter +1
END