UNION [ALL]과 함께 SELECT INTO 절을 사용할 수 있습니까?

UNION [ALL]과 함께 SELECT INTO 절을 사용할 수 있습니까?

---

SQL Server에서는 Customers 테이블에서 tmpFerdeen으로 100 개의 레코드를 삽입합니다.

SELECT top(100)*
INTO tmpFerdeen
FROM Customers

UNION ALL SELECT에서 SELECT INTO를 수행 할 수 있습니까?

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

INTO 절을 추가 할 위치가 확실하지 않습니다.

---

이것은 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

---

이를 위해 파생 테이블이 전혀 필요하지 않습니다.

그냥 INTO후를 넣어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

---

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

이 "Blablabal"이 필요합니다

---

MS Access 쿼리의 경우 다음과 같이 작동했습니다.

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
) 

이것은 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

---

나는 이렇게 할 것입니다 :

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

---

솔루션으로 볼 수있는 과제 :

FROM( 
SELECT top(100) *
    FROM Customers 
UNION
    SELECT top(100) *  
    FROM CustomerEurope 
UNION 
    SELECT top(100) *  
    FROM CustomerAsia 
UNION
    SELECT top(100) *  
    FROM CustomerAmericas
)

이것은 RAM에 상주하는 창 데이터 세트를 작성하고 더 큰 데이터 세트에서이 솔루션은 먼저 파티션을 작성해야하고 파티션을 사용하여 임시 테이블에 기록하므로 심각한 성능 문제를 발생시킵니다.

더 나은 해결책은 다음과 같습니다.

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!

---

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)

---

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
)

참고URL : https://stackoverflow.com/questions/591382/is-it-possible-to-use-the-select-into-clause-with-union-all