Is it possible to use the SELECT INTO clause with UNION [ALL]?
SqlSql ServerSql Problem Overview
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.
Sql Solutions
Solution 1 - 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
Solution 2 - Sql
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 - Sql
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 - Sql
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 - Sql
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
Solution 6 - Sql
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!
Solution 7 - Sql
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)
Solution 8 - Sql
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
)