Select into Using Union Query

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.

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

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

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*'
    )

select into using union

You forgot the GROUP BY in your union selects.

SELECT x.* INTO 
 NewTABLE FROM
   (SELECT val1, sum(Iif(val2<0,0,val2)) as PositiveVal2 
    FROM TABLE1 GROUP BY Val1
    UNION 
    SELECT val1, sum(Iif(val2<0,0,val2)) as PositiveVal2 
    FROM TABLE2 GROUP BY Val1) x

You must either aggregate or GROUP BY all select fields in an aggregate query.

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

SELECT Queries with Union and order into a new temp table is not working

I think it is icorrect syntax because of "Order by" Statement, You should put order by Out of bracket , and you also use case after order by So Please check is that case Statment is correct

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

Insert into table variable with union

INSERT INTO @table(a,b,c,d)
SELECT  a,b,c,d 
FROM   table1

UNION

SELECT a,b,c,d 
FROM table2

You do not need to use the Values clause when Inserting data using SELECT statement. Therefore I have removed the VALUES bit from it and just simply doing a UNION of rows being returned from both SELECT queries.

Sql server supports the syntax for INSERT statement like

INSERT INTO Table_Name(Col1, COl2. Col3...)
SELECT Col1, COl2. Col3...
FROM Other_Table_Name

This will insert that result set returned by the select statement into target table. In your case the Result is a UNION of two selects therefore it is not any different from a single select.

---

Related Topics