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

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

SELECT top(100)*
INTO tmpFerdeen
FROM Customers

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

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.

Solution 1:

This works in 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

Solution 2:

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

Just put the INTO after the first 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

Solution 3:

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

Solution 4:

For MS Access queries, this worked:

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

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

Solution 5:

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