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.
This question is related to
sql
sql-server
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
)
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
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)
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
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!
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
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
Source: Stackoverflow.com