SQL 是否可以将 SELECT INTO 子句与 UNION [ALL] 一起使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/591382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to use the SELECT INTO clause with UNION [ALL]?
提问by Ferdeen
In SQL Server this inserts 100 records, from the Customers table into tmpFerdeen :-
在 SQL Server 中，这会从客户表中插入 100 条记录到 tmpFerdeen 中：-
SELECT top(100)*
INTO tmpFerdeen
FROM Customers
Is it possible to do a SELECT INTO across a UNION ALL SELECT :-
是否可以在 UNION ALL SELECT 中执行 SELECT INTO :-
SELECT top(100)*
FROM Customers
UNION All
SELECT top(100)*
FROM CustomerEurope
UNION All
SELECT top(100)*
FROM CustomerAsia
UNION All
SELECT top(100)*
FROM CustomerAmericas
Not too sure where to add the INTO clause.
不太确定在哪里添加 INTO 子句。
回答by Chris Van Opstal
This works in SQL Server:
这适用于 SQL Server：
SELECT * INTO tmpFerdeen FROM (
SELECT top 100 *
FROM Customers
UNION All
SELECT top 100 *
FROM CustomerEurope
UNION All
SELECT top 100 *
FROM CustomerAsia
UNION All
SELECT top 100 *
FROM CustomerAmericas
) as tmp
回答by Martin Smith
You don't need a derived table at all for this.
为此，您根本不需要派生表。
Just put the INTOafter the first SELECT
只需将INTO后放在第一个SELECT
SELECT top(100)*
INTO tmpFerdeen
FROM Customers
UNION All
SELECT top(100)*
FROM CustomerEurope
UNION All
SELECT top(100)*
FROM CustomerAsia
UNION All
SELECT top(100)*
FROM CustomerAmericas
回答by user1006743
SELECT * INTO tmpFerdeen FROM
(SELECT top(100)*
FROM Customers
UNION All
SELECT top(100)*
FROM CustomerEurope
UNION All
SELECT top(100)*
FROM CustomerAsia
UNION All
SELECT top(100)*
FROM CustomerAmericas) AS Blablabal
This "Blablabal" is necessary
这个“Blablabal”是必要的
回答by Bobby
For MS Access queries, this worked:
对于 MS Access 查询，这有效：
SELECT * INTO tmpFerdeen FROM(
SELECT top(100) *
FROM Customers
UNION All
SELECT top(100) *
FROM CustomerEurope
UNION All
SELECT top(100) *
FROM CustomerAsia
UNION All
SELECT top(100) *
FROM CustomerAmericas
)
This did NOT work in MS Access
这在 MS Access 中不起作用
SELECT top(100) *
INTO tmpFerdeen
FROM Customers
UNION All
SELECT top(100) *
FROM CustomerEurope
UNION All
SELECT top(100) *
FROM CustomerAsia
UNION All
SELECT top(100) *
FROM CustomerAmericas
回答by Praveen R
I would do it like this:
我会这样做：
SELECT top(100)* into #tmpFerdeen
FROM Customers
Insert into #tmpFerdeen
SELECT top(100)*
FROM CustomerEurope
Insert into #tmpFerdeen
SELECT top(100)*
FROM CustomerAsia
Insert into #tmpFerdeen
SELECT top(100)*
FROM CustomerAmericas
回答by Chris Peterson
The challenge I see with the solution:
我在解决方案中看到的挑战：
FROM(
SELECT top(100) *
FROM Customers
UNION
SELECT top(100) *
FROM CustomerEurope
UNION
SELECT top(100) *
FROM CustomerAsia
UNION
SELECT top(100) *
FROM CustomerAmericas
)
is that this creates a windowed data set that will reside in the RAM and on larger data sets this solution will create severe performance issues as it must first create the partition and then it will use the partition to write to the temp table.
是因为这会创建一个窗口数据集，该数据集将驻留在 RAM 中，而在更大的数据集上，该解决方案将产生严重的性能问题，因为它必须首先创建分区，然后将使用该分区写入临时表。
A better solution would be the following:
更好的解决方案如下：
SELECT top(100)* into #tmpFerdeen
FROM Customers
Insert into #tmpFerdeen
SELECT top(100)*
FROM CustomerEurope
Insert into #tmpFerdeen
SELECT top(100)*
FROM CustomerAsia
Insert into #tmpFerdeen
SELECT top(100)*
FROM CustomerAmericas
to select insert into the temp table and then add additional rows. However the draw back here is if there are any duplicate rows in the data.
选择插入到临时表中，然后添加其他行。然而，这里的缺点是数据中是否有任何重复的行。
The Best Solution would be the following:
最佳解决方案如下：
Insert into #tmpFerdeen
SELECT top(100)*
FROM Customers
UNION
SELECT top(100)*
FROM CustomerEurope
UNION
SELECT top(100)*
FROM CustomerAsia
UNION
SELECT top(100)*
FROM CustomerAmericas
This method should work for all purposes that require distinct rows. If, however, you want the duplicate rows simply swap out the UNION for UNION ALL
此方法应该适用于需要不同行的所有目的。但是，如果您想要重复的行，只需将 UNION 换成 UNION ALL
Best of luck!
祝你好运！
回答by Ryan
Maybe try this?
也许试试这个？
SELECT * INTO tmpFerdeen (
SELECT top(100)*
FROM Customers
UNION All
SELECT top(100)*
FROM CustomerEurope
UNION All
SELECT top(100)*
FROM CustomerAsia
UNION All
SELECT top(100)*
FROM CustomerAmericas)
回答by achinda99
Try something like this: Create the final object table, tmpFerdeen with the structure of the union.
尝试这样的事情：创建最终的对象表，tmpFerdeen 与联合的结构。
Then
然后
INSERT INTO tmpFerdeen (
SELECT top(100)*
FROM Customers
UNION All
SELECT top(100)*
FROM CustomerEurope
UNION All
SELECT top(100)*
FROM CustomerAsia
UNION All
SELECT top(100)*
FROM CustomerAmericas
)
