CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Ray
Ray

Reputation: 3449

How to move UNION query results to a new table?

I have a sample query as shown below:

SELECT     *
FROM         [#temp1]
UNION
SELECT     *
FROM         [#temp2]
UNION
SELECT     *
FROM         [#temp3]
UNION
SELECT     *
FROM         [#temp4]
UNION
SELECT     *
FROM         [#temp5]

How do I move this request of these queries into a new table? Note: My verison of SQL is: 

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

I tried another Stackoverflow answer I found, i.e. 

CREATE TABLE managers AS SELECT * FROM employees WHERE desg = 'MANAGER';

But I get an error of : Incorrect syntax near the keyword 'as'.

Here was my full query that failed with the above error:

CREATE TABLE #temp_UNION as


SELECT     *
FROM         [#temp1]
UNION
SELECT     *
FROM         [#temp2]
UNION
SELECT     *
FROM         [#temp3]
UNION
SELECT     *
FROM         [#temp4]
UNION
SELECT     *
FROM         [#temp5]

Any suggestions please on how I'm goofing up?

Thank you, Ray

Upvotes: 23

Views: 94705

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

or you dont need to use derived table. You can do this too

SELECT *   INTO  #temp_UNION  
FROM         [#temp1]     
UNION     
SELECT     *     FROM         [#temp2]     
UNION     SELECT     *     FROM         [#temp3]     
UNION     SELECT     *     FROM         [#temp4]     
UNION     SELECT     *     FROM         [#temp5]

Upvotes: 2

Ovais Khatri
Ovais Khatri

Reputation: 3211

insert into temp_UNION
select * from (
SELECT     *
FROM         [#temp1]
UNION
SELECT     *
FROM         [#temp2]
UNION
SELECT     *
FROM         [#temp3]
UNION
SELECT     *
FROM         [#temp4]
UNION
SELECT     *
FROM         [#temp5]
)

Upvotes: 3

Chandu
Chandu

Reputation: 82913

In SQL Server you have to use 

SELECT <COLUMNS_LIST>
  INTO <NEW_TABLE_NAME>
  FROM <TABLES, WHERE ETC>

More information @ http://msdn.microsoft.com/en-us/library/ms188029.aspx

Try this:

SELECT *
  INTO  #temp_UNION 
FROM
(
        SELECT     *
    FROM         [#temp1]
    UNION
    SELECT     *
    FROM         [#temp2]
    UNION
    SELECT     *
    FROM         [#temp3]
    UNION
    SELECT     *
    FROM         [#temp4]
    UNION
    SELECT     *
    FROM         [#temp5]
) a

Upvotes: 32

Related Questions