SQL: While loop in While Loop

Question

I am trying to insert 10K names in Parent table, Using 10 described names while adding number in the back. But managing to write only first thousand. I need your eyes to see where I am failing. I do understand that @i is not increesing but dont know why.

DECLARE @TempNameTable table (ID int , Name varchar (50))
DECLARE @i int = 1,
    @tempNameValue varchar(50),
    @randNumber int = 1

INSERT INTO @TempNameTable VALUES 
     (1,'Jonas'), (2,'Petras'),(3,'Antanas')
   , (4,'Stasys'), (5,'Dainius'), (6,'Giedrius')
   , (7,'Mindaugas'), (8,'Povilas'), (9,'Kestutis')
   , (10,'Darius')


WHILE ((SELECT COUNT(Name) FROM Parent) < 10000)
BEGIN
WHILE @i < 11
    BEGIN
        SET @tempNameValue = CASE
            WHEN @i = 1 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            WHEN @i = 2 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            WHEN @i = 3 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            WHEN @i = 4 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            WHEN @i = 5 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            WHEN @i = 6 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            WHEN @i = 7 THEN
                (SELECT Name from @TempNameTable WHERE ID = @i)
            END
        WHILE @randNumber < 1000
        BEGIN
            INSERT INTO Parent VALUES 
             (@tempNameValue + CAST(@randNumber as varchar(1000)))
            SET @randNumber = @randNumber + 1
        END
    SET @i = @i + 1
    END

 END

Why use a CASE if your query is the same? Your @i variable guarantuee a different where condition — Joe Taras
– Joe Taras, Commented Jan 17, 2017 at 13:40
Tag the dbms you're using (perhaps SQL Server?) That code is far from ANSI SQL compliant. — jarlh
– jarlh, Commented Jan 17, 2017 at 13:40
I need somehow to change names not only number in the back. Whats wrong with CASE? — LTnewbie
– LTnewbie, Commented Jan 17, 2017 at 13:41
SQL isn't a procedural language. You should rewrite this as a set based operation. — Chris Pickford
– Chris Pickford, Commented Jan 17, 2017 at 13:45

Joe Taras · Accepted Answer · 2017-01-17 14:11:28Z

3

I suppose your query becomes:

DECLARE @TempNameTable table (ID int , Name varchar (50))
DECLARE @tempNameValue varchar(50),
@randNumber int = 1

INSERT INTO @TempNameTable VALUES
(1,'Jonas'), (2,'Petras'), (3,'Antanas'),
(4,'Stasys'), (5,'Dainius'), (6,'Giedrius'),
(7,'Mindaugas'), (8,'Povilas'), (9,'Kestutis'), (10,'Darius')

WHILE ((SELECT COUNT(Name) FROM Parent) < 10000)
BEGIN
    SET @randNumber = 1
    WHILE @randNumber <= 1000
    BEGIN
        INSERT INTO Parent 
        SELECT Name + CAST(@randNumber as varchar(1000))
        FROM @TempNameTable
        SET @randNumber = @randNumber + 1
    END
END

edited Jan 17, 2017 at 14:11

answered Jan 17, 2017 at 13:47

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

LTnewbie Over a year ago

But it still writes only 1000 entries not 10k that i need. But this much simpler way than with CASE. foolish me :/

Hans Keﬆing Over a year ago

Shouldn't you reset @randnumber before the inner loop?

Joe Taras Over a year ago

@LTnewbie: more simple

LTnewbie Over a year ago

Yes, that's the answer. @randNumber is not reset after 1000 is met

LTnewbie Over a year ago

Thanks Joe. I was over thinking and not seeing simplier way to do the same :)

|

Wheels73 · Accepted Answer · 2017-01-17 14:01:18Z

I'd do it like the below. Just tested and it outputs each name * 1,000 rows. It's more verbose but its obvious what it's doing and it works.

Declare @Jonas varchar(20) = 'Jonas'
Declare @Petras varchar(20) = 'Petras'
Declare @Antanas varchar(20) = 'Antanas'
Declare @Stasys varchar(20) = 'Stasys'
Declare @Dainius varchar(20) = 'Dainius'
Declare @Giedrius varchar(20) = 'Giedrius'
Declare @Mindaugas varchar(20) = 'Mindaugas'
Declare @Povilas varchar(20) = 'Povilas'
Declare @Kestutis varchar(20) = 'Kestutis'
Declare @Darius varchar(20) = 'Darius'

Declare @TempJonas varchar(20)
Declare @TempPetras varchar(20)
Declare @TempAntanas varchar(20)
Declare @TempStasys varchar(20)
Declare @TempDainius varchar(20)
Declare @TempGiedrius varchar(20)
Declare @TempMindaugas varchar(20)
Declare @TempPovilas varchar(20)
Declare @TempKestutis varchar(20)
Declare @TempDarius varchar(20)

Declare @NameIncrement int = 0

WHILE @NameIncrement <= 1000
BEGIN
    Set @TempJonas = @Jonas + CONVERT(varchar(6),@NameIncrement)
    Set @TempPetras = @Petras + CONVERT(varchar(6),@NameIncrement)
    Set @TempAntanas = @Antanas + CONVERT(varchar(6),@NameIncrement)
    Set @TempStasys = @Stasys + CONVERT(varchar(6),@NameIncrement)
    Set @TempDainius = @Dainius + CONVERT(varchar(6),@NameIncrement)
    Set @TempGiedrius = @Giedrius + CONVERT(varchar(6),@NameIncrement)
    Set @TempMindaugas = @Mindaugas + CONVERT(varchar(6),@NameIncrement)
    Set @TempPovilas = @Povilas + CONVERT(varchar(6),@NameIncrement) 
    Set @TempKestutis = @Kestutis + CONVERT(varchar(6),@NameIncrement)
    Set @TempDarius = @Darius + CONVERT(varchar(6),@NameIncrement)   

    Insert Into Parent
    Select @TempJonas

    Insert Into Parent
    Select @TempPetras

    Insert Into Parent
    Select @TempAntanas

    Insert Into Parent
    Select @TempStasys

    Insert Into Parent
    Select @TempDainius

    Insert Into Parent
    Select @TempGiedrius

    Insert Into Parent
    Select @TempMindaugas

    Insert Into Parent
    Select @TempPovilas

    Insert Into Parent
    Select @TempKestutis

    Insert Into Parent
    Select @TempDainius

    Set @NameIncrement = @NameIncrement + 1     
END

SqlZim · Accepted Answer · 2017-01-26 18:33:08Z

Set based answer without loops:

rextester: http://rextester.com/EBM78452

declare @TempNameTable table (id int , Name varchar (50))
insert into @TempNameTable values
    (1,'Jonas'), (2,'Petras'), (3,'Antanas')
  , (4,'Stasys'), (5,'Dainius'), (6,'Giedrius')
  , (7,'Mindaugas'), (8,'Povilas'), (9,'Kestutis')
  , (10,'Darius');

if object_id('tempdb..#r') is not null drop table #r;
create table #r (n int not null primary key, r int not null);

with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, d as (
  select n=row_number() over (order by (select 1))
       , r=convert(int,((rand(checksum(newid())) * 10)+1))
    from         n as deka
      cross join n as hecto
      cross join n as kilo   
      cross join n as tenK
    )
insert into #r (n, r)
select n, r from d;
--insert into Parent
select Name=Name+convert(varchar(13),n)
  from #r as r
    inner join @TempNameTable t on r.r=t.id

This uses convert(int,((rand(checksum(newid())) * 10)+1)) to generate a random number from 1 to 10, and uses that to join the temp table #r to @TempNameTable

Collectives™ on Stack Overflow

SQL: While loop in While Loop

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related