需要将4个表TTA、TTB 、TTC、TTD日期介于（2016-10-1）至（2016-10-15）的记录更新到一个新建的表中

--建立测试数据
create table #表A(AA1 INT,AA2 INT,AA3 INT,TTA datetime)
create table #表B(BB1 INT,BB2 INT,BB3 INT,TTB datetime)
create table #表C(CC1 INT,CC2 INT,CC3 INT,TTC datetime)
create table #表D(DD1 INT,DD2 INT,DD3 INT,TTD datetime)

insert into #表a
select 1,1,1,'2016-09-9' union all
select 1,1,1,'2016-09-29' union all
select 1,1,1,'2016-10-1' union all
select 1,1,1,'2016-10-12' union all
select 1,1,1,'2016-10-16'

insert into #表b select AA1+1,AA2+1,AA3+1,TTA+1 from #表a
insert into #表c select AA1+2,AA2+2,AA3+2,TTA+2 from #表a
insert into #表d select AA1+3,AA2+3,AA3+3,TTA+3 from #表a

--日期介于（2016-10-1）至（2016-10-15）的记录更新到新建
--1、新表已经存在时，先模拟建立表E
create table #表E(EE0 INT,EE1 INT,EE2 INT,EE3 INT,TTE datetime) --新表字段个数和位置和其它表不同
--更新到新表E
insert into #表E(EE1,EE2,EE3,TTE) --如果新表E的结构和其它表相同，就不要()里面的
select * from #表a where tta >='2016-10-01' and tta <='2016-10-15' union all
select * from #表b where ttb >='2016-10-01' and ttb <='2016-10-15' union all
select * from #表c where ttc >='2016-10-01' and ttc <='2016-10-15' union all
select * from #表d where ttd >='2016-10-01' and ttd <='2016-10-15'

select * from #表E
DROP TABLE #表E
GO

--2、新表不存在时
select AA1 AS EE1,AA2 AS EE2,AA3 AS EE3,TTA AS TTE into #表E from #表A where tta >='2016-10-01' and tta <='2016-10-15' union all
select * from #表b where ttb >='2016-10-01' and ttb <='2016-10-15' union all
select * from #表c where ttc >='2016-10-01' and ttc <='2016-10-15' union all
select * from #表d where ttd >='2016-10-01' and ttd <='2016-10-15'
--查看数据，并删除
select * from #表E

GO
DROP TABLE #表A
DROP TABLE #表B
DROP TABLE #表C
DROP TABLE #表D
DROP TABLE #表E

[此贴子已经被作者于2016-10-16 09:06编辑过]