Path: csiph.com!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: microsoft.public.sqlserver.programming
Subject: Re: [SAP B1] create a VIEW with UNION and WHILE
Date: Wed, 31 Mar 2021 22:32:13 +0200
Organization: Erland Sommarskog
Lines: 38
Message-ID: <XnsACFEE5428854AYazorman@127.0.0.1>
References: <XnsACFE7390C25D5ammammatatiscalineti@127.0.0.1>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: reader02.eternal-september.org; posting-host="939b69d9db0387922e73c91ec9fdd514"; logging-data="26690"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19FytlL0KZxg70rjIGHzBv3"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:e5IiQFy1F+dpYSR53uqG/XmnGT8=
Xref: csiph.com microsoft.public.sqlserver.programming:31373

Ammammata (ammammata@tiscalinet.it) writes:
> 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

That's not a good start for a view. A view is a single SELECT statement.

It seems that this should make it for you:

CREATE VIEW yourview AS
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
UNION ALL
select a,b,V.n as c
from tableFIVE
cross join (VALUES(1), (2), (3), (4), (5), (6), (7), 
                 (8), (9), (10), (11), (12), (13)) AS V(n)


THe error message "unable to parse" sounds like you are using the view
designer. Stay out of that tool, it's quite crippled and may not parse 
the above either. Just run the above in a query window.