代码之家 › 专栏 › 技术社区 › Ferdeen

是否可以将select into子句与union[all]一起使用?

sql-server sql

117

Ferdeen · 技术社区 · 17 年前

在SQL Server中,这会将100条记录从customers表插入tmpferdeen中:

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子句。

7 回复 | 直到 8 年前

170

Chris Van Opstal 17 年前

这在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

111

Martin Smith 13 年前

您根本不需要派生表。

只要放 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

Jav_Rock Rahul N 13 年前

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”是必要的

Bobby 9 年前

对于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

Matheus Lacerda Fidan Bacaj 8 年前

我会这样做:

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

-1

PhiLho 17 年前

也许试试这个?

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)

-3

achinda99 17 年前

尝试这样做:用union的结构创建最终的对象表tmpferdeen。

然后

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
)