sql - 如何将UNION查询结果移动到新表？

Question

我有一个示例查询，如下所示:

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

如何将这些查询的请求移至新表中？注意:我的 SQL 版本是:

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) 

我尝试了我找到的另一个 Stackoverflow 答案，即

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

但我收到错误:关键字“as”附近的语法不正确。

这是我因上述错误而失败的完整查询:

CREATE TABLE #temp_UNION as


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

关于我如何搞砸，有什么建议吗？

谢谢你，雷

Answer 1

在 SQL Server 中你必须使用

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

更多信息@ http://msdn.microsoft.com/en-us/library/ms188029.aspx

试试这个:

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