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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server