SQL Server while loop union all [closed] SQL Server while loop union all [closed] sql sql

SQL Server while loop union all [closed]

Instead of trying to use UNION I would use a temp table or temp table variable to merge the result sets

CREATE TABLE #Temp(  <COLUMNS>) DECLARE @position INT SET @position = -1 WHILE(@position < 1) BEGIN    INSERT INTO #Temp (<COLUMNS>)    SELECT * FROM mytable    SET @position = @position + 1 END SELECT * FROM #Temp

That query makes no sense. To use UNION, you need to be selecting from a second table.

SQL Server essentially sees:

SELECT * FROM mytable      UNION ALL 

With no second table after the UNION.

This way gets rid of the the UNION statement and avoids a loop altogether:

DECLARE @position INTSET @position = 5DECLARE @n TABLE (n int)INSERT INTO @n SELECT ROW_NUMBER() OVER (ORDER BY name) AS xFROM syscolumnsSELECT t.* FROM dbo.Table t    CROSS JOIN @n n WHERE n <= @position