是否可以将 SELECTINTO 子句与 UNION [ ALL ]一起使用？

在 SQLServer 中，这会将100条记录从“客户”表插入到 tmpFerdeen:-

SELECT top(100)*
INTO tmpFerdeen
FROM Customers

是否可以跨所有选中的联合执行 SELECTINTO:-

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

不太确定在哪里添加 INTO 子句。

301776 次浏览
小开

尝试这样做: 使用联合的结构创建最终的对象表 tmpFerdeen。

然后

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
)
小开

试试这个？

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)
小开
最佳答案

这在 SQLServer 中有效:

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
小开 
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

这个“喋喋不休”是必要的

小开

完全不需要派生表。

只要把 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
小开

对于 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
)

这在 MSAccess 中不起作用

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
小开

我会这样做:

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
小开

我认为解决方案面临的挑战是:

FROM(
SELECT top(100) *
FROM Customers
UNION
SELECT top(100) *
FROM CustomerEurope
UNION
SELECT top(100) *
FROM CustomerAsia
UNION
SELECT top(100) *
FROM CustomerAmericas
)

这将创建一个窗口数据集，将驻留在 RAM 和较大的数据集，这个解决方案将创建严重的性能问题，因为它必须首先创建分区，然后它将使用分区写到临时表。

更好的解决办法如下:

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

选择插入到临时表中，然后添加其他行。然而，这里的缺点是数据中是否有任何重复的行。

最佳解决办法如下:

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

此方法应适用于所有需要不同行的目的。但是，如果您想要重复的行，只需将 UNION 换成 UNION ALL

祝你好运！


提交答案