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

Question

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.

Answer 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

Answer 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

Answer 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

Answer 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

Answer 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

Answer 6

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.

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

Best of luck!

Answer 7

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)

Answer 8

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

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
)