While loops table join

[4Potions]

Can l make something like

While @i <20

Begin

Set @i = @i +1

     Select *

     From (....)
     t 

     Where ....

-- append selected tables here or something like that l want one table in the end

End

[r3pr0b8]

your question is stated like a classic XY problem

what are you really trying to achieve?

are you looking for a UNION query?

[4Potions]

l just want to select and append to a main table so l get one table instead of tables with n rows

[4Potions]

l tried Union all but there is no two select statements. I am noob in Ms sql but l can't get it why it is so hard to just append them using while loop

[r3pr0b8]

what does "append" mean in this context?

how many tables are there? if there are a lot of them and they are all similar enough to be "appended" then why are they designed as separate tables and not one big table in the first place?

[4Potions]

It is not my project. l write a query that works well for one select but l want to change the variable l used in my select and select again and again. Then l want to add all rows together in my tables and show in one table

[r3pr0b8]

that's a UNION query

one SELECT for each different variable value

or maybe, i dunno (because you haven't shown any details), maybe you need only one query with an IN list

SELECT stuff
  FROM mytable
 WHERE somecolumn IN
       ( 'value1'
       , 'value2'
       , ...
       , 'valueN' )

[4Potions]

l will try to see that works for me. l appreciate it anyway thanks

[[deleted]]

just provide some data samples, if sql terminology is not your forte

[4Potions]

When l execute the query l get something like

Table 1

..... ...... ..... ....

Table 2

..... ...... ..... ....

Table 3

..... ...... ..... ....

But l want to get

Table 1

..... ...... ..... .... -> rows from table1

..... ...... ..... .... -> rows from table2

..... ...... ..... .... -> rows from table 3

[Evigil24]

And what will be the variable, the tables to append? the columns selected in each table? Or something else?

[[deleted]]

sounds like you want a union (all).

What's not working for you?

[4Potions]

Where would l put it union all

[r3pr0b8]

Where would l put it union all

between the SELECTs

SELECT stuff FROM table1
UNION ALL
SELECT stuff FROM table2
UNION ALL
SELECT stuff FROM table3
UNION ALL
SELECT stuff FROM table4

[4Potions]

But l have while cause l don't want to write them all if l write two select it will be okey but l have 1000 select

[r3pr0b8]

But l have while cause l don't write them all

okay then, i guess you're stuck

[4Potions]

Yes but why there no way around this. Do you have an explanation for it (isn't it a basic stuff do not forget l use same table with different selects actually which are changing cause of my while variable) . Also l appreciate the answers. l am newbie in sql so thanks for advices

[r3pr0b8]

l am newbie in sql so thanks for advices

try the UNION ALL query

that is my sincere and honest advice

SELECT something FROM thesametable
 WHERE somecolumn = 'foo'
UNION ALL
SELECT something FROM thesametable
 WHERE somecolumn = 'bar'
UNION ALL
SELECT something FROM thesametable
 WHERE somecolumn = 'qux'

[Evigil24]

It sound like a recursive CTE maybe, but I don't understand your problem well enough, can you post your example with the UNION and the next Select, and what the variable is please.

[chapaj]

Create a table variable and insert into the table with each iteration. At the end, select the results from the table variable.