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