Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

People also ask

Can we use UNION in SELECT query?

The SQL UNION OperatorThe UNION operator is used to combine the result-set of two or more SELECT statements.

Can we use where clause with UNION in SQL?

Using the Where Clause With the UNION OperatorWe can use the WHERE clause in either one or both of the SELECT statements to filter out the rows being combined. We can also use the WHERE clause in only one of the SELECT statements in the UNION.

Can we use ORDER BY in UNION all in SQL?

The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).


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

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

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


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

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