Append (?) query

I'm trying to join 8 views (ViewA, ViewB, etc...) together
into one new table. All the views have 3 columns (ID,
result1, result2), as does the destination table. I'm
currently trying this method -
SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewA]
SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewB]
...etc...
but only ViewA is added to ANewTable and I get the error
message -
'There is already an object named 'ANewTable' in the
database.'
Any advice on how to resolve this would be greatly
appreciated.
Thanks.
1: Do a UNION between the SELECT statements:
SELECT col1, col2, ... FROM ViewA
UNION ALL
SELECT col1, col2, ... FROM ViewB
UNION ALL
SELECT col1, col2, ... FROM ViewC
2. Once that is sorted out and gives correct result, use it as a derived table:
SELECT col1, col2, ...
INTO aNewTable
FROM
(
<the above code goes here>
) AS d
Each SELECT INTO creates a new table, you cannot have several using the same table name. Also, don't
use SELECT *, that is begging for trouble in the future...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hagglingchad" <anonymous@.discussions.microsoft.com> wrote in message
news:088b01c4a608$d2b2f830$a401280a@.phx.gbl...
> I'm trying to join 8 views (ViewA, ViewB, etc...) together
> into one new table. All the views have 3 columns (ID,
> result1, result2), as does the destination table. I'm
> currently trying this method -
> SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewA]
> SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewB]
> ...etc...
> but only ViewA is added to ANewTable and I get the error
> message -
> 'There is already an object named 'ANewTable' in the
> database.'
> Any advice on how to resolve this would be greatly
> appreciated.
> Thanks.
|||Tibor,
This is great. Thanks very much. However, I'm not quite
sure of the relevance of 'AS d'.
Chad
>--Original Message--
>1: Do a UNION between the SELECT statements:
>SELECT col1, col2, ... FROM ViewA
>UNION ALL
>SELECT col1, col2, ... FROM ViewB
>UNION ALL
>SELECT col1, col2, ... FROM ViewC
>2. Once that is sorted out and gives correct result, use
it as a derived table:
>SELECT col1, col2, ...
>INTO aNewTable
>FROM
>(
><the above code goes here>
>) AS d
>
>Each SELECT INTO creates a new table, you cannot have
several using the same table name. Also, don't
>use SELECT *, that is begging for trouble in the future...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"hagglingchad" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:088b01c4a608$d2b2f830$a401280a@.phx.gbl...
together
>
>.
>
|||That is just an alias name for the derived table. Same as in below:
FROM tbla AS a JOIN tblb AS b
For a derived table, you *need* to define such a "correlation" name. Syntax requires it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hagglingchad" <anonymous@.discussions.microsoft.com> wrote in message
news:14f401c4a615$b83324a0$a601280a@.phx.gbl...[vbcol=seagreen]
> Tibor,
> This is great. Thanks very much. However, I'm not quite
> sure of the relevance of 'AS d'.
> Chad
> it as a derived table:
> several using the same table name. Also, don't
> wrote in message
> together