create a VIEW with UNION and WHILE

former_member651578 — Wed, 31 Mar 2021 09:16:39 GMT

I'm creating a VIEW (to be used as Excel pivot source) that collects data from five different tables. I coded it in MS SQL Server Management Studio and it works fine, but when I copy it to the VIEW I get the error (Unable to parse).

The SQL is like this:

SELECT *
INTO #tmp
FROM (
SELECT a,b,c
FROM tableONE
UNION ALL
SELECT a,b,c
FROM tableTWO
UNION ALL
SELECT a,b,c
FROM tableTHREE
UNION ALL
SELECT a,b,c
FROM tableFOUR
) tmp
DECLARE @cnt INT = 1
WHILE @cnt < 13
BEGIN
insert into #tmp
select a,b,@cnt as c
from tableFIVE
SET @cnt = @cnt + 1
END
select * from #tmp
DROP TABLE #tmp

As I wrote above, it WORKS, but not as a VIEW

The only "solution" I can imagine is to remove the #tmp table and make an UNION of all 16 (4+12) select statements

Any tip about this?

TIA,

Giovanni

topic create a VIEW with UNION and WHILE in Application Development and Automation Discussions

create a VIEW with UNION and WHILE