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

data should be inserted into another table upto 1 lakh but the three records should be repeated as shown in output — mohan111, Feb 18 '16 at 06:01
What exactly are you trying to do? Since you have assigned a value to `@EmployeeName `, the last assigned value is being reprinted till the condition `WHILE(@LoopCounter <= @MaxEmployeeId)` evaluates to `false`. Please explain your requirement clearly — Raj, Feb 18 '16 at 06:03
MaxEmployeeId is to give only numbers if I want 100000 records I will modify there for that purpose only @FelixPamittan — mohan111, Feb 18 '16 at 06:07
@Raj I just want below output if it is wrong with the query then modify according to requirement. I'm open for suggestions as I have never used loops — mohan111, Feb 18 '16 at 06:09
You just want those three records printed four times repetitively? Or is there some other condition? — Raj, Feb 18 '16 at 06:15

Khalid Amin · Answer 1 · 2016-02-18T06:35:47.513

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

edited Feb 18 '16 at 06:35

answered Feb 18 '16 at 06:10

Khalid Amin

872
3
12
26

it is giving the same result nothing much have happened – mohan111 Feb 18 '16 at 06:20
I see, you need to start from 1st record again. I am editing my response. – Khalid Amin Feb 18 '16 at 06:24
1

this going into infinite loop – mohan111 Feb 18 '16 at 06:29
Oops, just edit my answer. However, I am still not clear why you need this. I am sure there are much better ways to achieve your purpose. – Khalid Amin Feb 18 '16 at 06:36
I need to insert 5 lakhs records check my script anyway thanks for answering ...I'm confused a lot with loops – mohan111 Feb 18 '16 at 06:38

score 1 · Answer 2 · answered Feb 18 '16 at 06:28

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

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

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 |

edited May 23 '17 at 11:59

Community

1
1

answered Feb 18 '16 at 06:19

Felix Pamittan

31,544
7
41
67

yeah it is giving the result but it is printing simultaneously same record for 3 times and then other records I need like above output – mohan111 Feb 18 '16 at 06:21
it is giving the data exactly but take an example 'Basavaraj Biradar' it is repeating 3 times and then other record I need like Basavaraj Biradar next Shree Biradar next other record then again repeat the same – mohan111 Feb 18 '16 at 06:28
Are you sure? Have you look at the SQL Fiddle? – Felix Pamittan Feb 18 '16 at 06:29
yes it's working if need to create 5 lakhs records for the same table then I need to modify the (1) I need to extend them – mohan111 Feb 18 '16 at 06:35
If you want to limit the number of records to be generated, modify the first query's `MaxEmployeeId`. If you want to specify how many times the records must be repeated, modify the second query's `@RepeatTimes`. – Felix Pamittan Feb 18 '16 at 06:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103797/discussion-between-mohan111-and-felix-pamittan). – mohan111 Feb 18 '16 at 06:40

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

3 Answers3