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
