mssql sqlserver into 中使用 union(union all)、except、intersect关键字的方法分享


摘要:
下文分享在into关键字将组合出的结果集快速的插入到一张新表中,
如下所示:
实验环境:sql server 2008 R2



一. into中使用操作结果集关键字的用法分享

当我们要在select into中使用结果集操作关键字时,我们应在第一个查询结果集的from 函数前面加入into 操作语句,
也可以先生成结果集,然后再查询和插入结果集,如下例所示:

----select *into方式1   
select * into #tmpTable 
  from  [maomao365.com_A]
  except 
  select * from 
   [maomao365.com_B]
   intersect 
  select * from 
   [maomao365.com_C]
   union 
  select * from 
   [maomao365.com_D]

---select *into 方式2,将结果集采用括号限定符进行修饰和限制,如下所示
select * into #tmpTable 
  from 
(select * from  [maomao365.com_A]
  except 
  select * from 
   [maomao365.com_B]
   intersect 
  select * from 
   [maomao365.com_C]
   union 
  select * from 
   [maomao365.com_D]
) as tmp