Re: Append (?) query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 12:18:59 +0200

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.

Relevant Pages

  • Re: Perl DBI - Select across multiple Database
    ... Source database on using UNION clause. ... select col1, col2 from users@sourcedb ... I've been away from database for a while. ...
    (comp.lang.perl.misc)
  • Re: Optimizing SQL - Union
    ... select 'col1' as field, col1 as val from table1 ... select 'col2' as field, ...
    (comp.databases.ms-sqlserver)
  • Re: Desparate plea for help!!
    ... SELECT col1 AS id, ... UNION ALL ... SELECT col2, SUM, COUNT ... SELECT col3, SUM, COUNT ...
    (microsoft.public.sqlserver.programming)
  • Re: Optimizing SQL - Union
    ... select 'col1' union all ... Columnist, SQL Server Professional ... The results of this union query are selected into a temp table, ...
    (comp.databases.ms-sqlserver)
  • Re: Optimizing SQL - Union
    ... If you have SQL 2005, ... select 'col1' union all ... The results of this union query are selected into a temp table, ...
    (comp.databases.ms-sqlserver)