blocks|key|1617319|text|这在SQL+Server中有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1617320|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|code-block|syntax|javascript|1617321|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works in SQL Server:

<pre><code>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
</code></pre>

blocks|key|1617369|text|为此，您根本不需要派生表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1617370|只需将INTO放在第一个SELECT之后|offset|length|style|CODE|1617371|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|code-block|syntax|javascript|1617372|entityMap^0|0|3|4|C|6|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

You don't need a derived table at all for this.

Just put the <code>INTO</code> after the first <code>SELECT</code>

<pre><code>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
</code></pre>

blocks|key|1402692|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1402693|这个"Blablabal“是必要的|unstyled|1402694|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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
</code></pre>

This "Blablabal" is necessary

blocks|key|1402738|text|对于MS+Access查询，这是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402739|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
)+|code-block|syntax|javascript|1402740|这在MS+Access中不起作用|1402741|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|1402742|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For MS Access queries, this worked:

<pre><code>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
) 
</code></pre>

This did NOT work in MS Access

<pre><code>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
</code></pre>

blocks|key|1617453|text|我会这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1617454|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|code-block|syntax|javascript|1617455|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I would do it like this:

<pre><code>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
</code></pre>

blocks|key|1402804|text|我所看到的解决方案的挑战是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402805|FROM(+
SELECT+top(100)+*
++++FROM+Customers+
UNION
++++SELECT+top(100)+*++
++++FROM+CustomerEurope+
UNION+
++++SELECT+top(100)+*++
++++FROM+CustomerAsia+
UNION
++++SELECT+top(100)+*++
++++FROM+CustomerAmericas
)|code-block|syntax|javascript|1402806|这将创建一个将驻留在RAM和更大数据集上的窗口数据集，该解决方案将产生严重的性能问题，因为它必须首先创建分区，然后使用该分区写入临时表。|1402807|更好的解决方案是：|1402808|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|1402809|若要选择“插入到临时表中，然后添加其他行”，请执行以下操作。然而，这里的问题是如果数据中有任何重复的行。|1402810|最佳解决方案如下：|1402811|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|1402812|此方法应适用于需要不同行的所有用途。但是，如果想要复制行，只需将UNION替换为UNION+ALL|1402813|祝你好运！|1402814|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

The challenge I see with the solution:

<pre><code>FROM( 
SELECT top(100) *
 FROM Customers 
UNION
 SELECT top(100) * 
 FROM CustomerEurope 
UNION 
 SELECT top(100) * 
 FROM CustomerAsia 
UNION
 SELECT top(100) * 
 FROM CustomerAmericas
)
</code></pre>

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. 

A better solution would be the following:

<pre><code>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
</code></pre>

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:

<pre><code>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
</code></pre>

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 

Best of luck!

blocks|key|1402585|text|也许可以试试这个？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402586|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)|code-block|syntax|javascript|1402587|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Maybe try this?

<pre><code>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)
</code></pre>

blocks|key|1402577|text|尝试这样的操作:使用联合的结构创建最终的对象表tmpFerdeen。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402578|然后|1402579|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
)|code-block|syntax|javascript|1402580|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try something like this: Create the final object table, tmpFerdeen with the structure of the union.

Then

<pre><code>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
)
</code></pre>

In SQL Server this inserts 100 records, from the Customers table into tmpFerdeen :-

<pre><code>SELECT top(100)*
INTO tmpFerdeen
FROM Customers
</code></pre>

Is it possible to do a SELECT INTO across a UNION ALL SELECT :-

<pre><code>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
</code></pre>

Not too sure where to add the INTO clause.

Is it possible to use the SELECT INTO clause with UNION [ALL]?

在SQL Server中，这会将100条记录从Customers表插入到tmpFerdeen :-SELECT top(100)*INTO tmpFerdeenFROM Customers是否可以跨UNION ALL SELECT执行SELECT INTO :-SELECT top(100)* FROM CustomersUNION AllSELECT top(100)* FROM Custome

问可以将SELECT INTO子句与UNION [ALL]一起使用吗？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问可以将SELECT INTO子句与UNION [ALL]一起使用吗？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问可以将SELECT INTO子句与UNION [ALL]一起使用吗？
EN