How to Use the Select into Clause with Union [All]

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

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

SELECT INTO USING UNION QUERY

You have to define a table alias for a derived table in SQL Server:

SELECT x.* 
  INTO [NEW_TABLE]
  FROM (SELECT * FROM TABLE1
        UNION
        SELECT * FROM TABLE2) x

"x" is the table alias in this example.

How to use `SELECT ... INTO` with UNION?

You are missing a SELECT for table2

SELECT *
INTO   #tmp
FROM   table1
UNION
SELECT *
FROM   table2 

SQL Fiddle

with cte union all select into

when mutiple CTE you have to remove with only ,.

it looks like :

with cte as (
  select 1 id
),cte2 as (
  select 2 id
)
,cte3 as (
  select * from CTE
  union all
  select * from CTE2
)
insert into T select * from CTE3;

---

Result:

| id |
|----|
|  1 |
|  2 |

---

TEST DDL:

CREATE TABLE T
    (id int)
;

---

Result:

| ID |
|----|
|  1 |
|  2 |

---

SQL Fiddle

MS Access SQL: Using SELECT INTO with a UNION ALL query

You are getting the error

An action query cannot be used as a row source.

because a SELECT ... INTO NewTableName FROM ... query is an action query (like an INSERT, UPDATE, or DELETE query) which does not return a result set. Therefore the UNION ALL has nothing to work with. So, for example, this won't work (resulting in the above error message):

    SELECT DISTINCT LastName 
    INTO Foo 
    FROM Clients 
    WHERE LastName LIKE 'D*'
UNION ALL
    SELECT DISTINCT LastName 
    INTO Foo 
    FROM Clients 
    WHERE LastName LIKE 'T*'

However, you can reorganize that query as follows, which does work:

SELECT *
INTO Foo
FROM
    (
        SELECT DISTINCT LastName 
        FROM Clients 
        WHERE LastName LIKE 'D*'
    UNION ALL
        SELECT DISTINCT LastName 
        FROM Clients 
        WHERE LastName LIKE 'T*'
    )

Generate a WITH clause/UNIONs from a SELECT

It would be easier to use xmltable or (json_table for Oracle 12+) for such purposes.

Example with xmltable:

1. Just aggregate all the required data into xmltype:
   you can use xmltype(cursor(select...from...)):

select xmltype(cursor(select * from test)) xml from dual;

or dbms_xmlgen.getxmltype(query_string):

select dbms_xmlgen.getxmltype('select * from test') xml from dual;

2. then you can use the returned XML with

xmltable('/ROWSET/ROW' passing xmltype(your_xml) columns ...)

Example:

select *
from xmltable(
        '/ROWSET/ROW'
        passing xmltype(q'[<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ASSET_ID>10</ASSET_ID>
  <VERTEX_NUM>1</VERTEX_NUM>
  <X>118.56</X>
  <Y>3.8</Y>
 </ROW>
 <ROW>
  <ASSET_ID>10</ASSET_ID>
  <VERTEX_NUM>2</VERTEX_NUM>
  <X>118.62</X>
  <Y>1.03</Y>
 </ROW>
</ROWSET>
        ]')
    columns 
        asset_id,vertex_num,x,y
) test

Full example on DBFiddle: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=036b718f2b18df898c3e3de722c97378

How to use UNION while inserting in a table using SELECT clause from many tables in SQL Server 2000

The pseudo is too cryptic (reduced?)
It is very unlikely to get 2 columns per cross join of 2 tables in each of the union components

INSERT INTO T1(A,B)
(SELECT * FROM E,R)
UNION
(SELECT * FROM Z,X)

Note: If you have ANY order by clause at all, it must be at the end of the union

INSERT T1(A,B)
SELECT P,Q FROM E,R
UNION
SELECT R,S FROM Z,X

@updated based on error text "Server: Msg 104, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if the statement contains a UNION operator"

This occurs when you have a union that attempts to perform ORDER BY on a column that does not appear in the result. Consider a normal ORDER BY involving non-selected columns

select top 10 name from syscolumns order by xtype

The rows are consistent and the query can be satisfied. However, if you did

select top 10 name from syscolumns where xtype > 50
union all
select top 10 name from syscolumns where xtype < 50
order by xtype

EVEN IF xtype exists in both parts of the UNION, but the time it gets presented to ORDER BY (which works at the END over the entire result set), the column is not there. You would have to rewrite it (if you didn't want to show xtype) as

select name from (
    select top 10 name, xtype from syscolumns where xtype > 50
    union all
    select top 10 name, xtype from syscolumns where xtype < 50
) x
order by xtype

Hope that helps

Using multiple UNION ALL statements in WITH clause

In the recursion you want to go on with x on a y match and with y on an x match. Use CASE WHEN to check which one matches.

, rokon (x, y) AS (
  SELECT x, y FROM testver
  UNION ALL
  SELECT CASE WHEN gy1.szulonek = r1.x THEN r1.y ELSE r1.x END, gy1.gyereke
  FROM rokon r1
  JOIN gyerek gy1 ON gy1.szulonek IN (r1.x, r1.y)
)

How to change a UNION to a IN clause?

You may use a CASE expression here with a single query:

SELECT
    CASE id WHEN @firstId  THEN 1
            WHEN @secondId THEN 2
            WHEN @thirdId  THEN 3 END AS val,
    mycolumn
FROM mytable
WHERE
    id IN (@firstId, @secondId, @thirdId);

If you wish to also order by the computed column, then add ORDER BY val to the end of the above query.

---

Related Topics