环境:SQL Server 2k
我有两个表,结构一致
A_Inc表做为增量表
A_Full表做为全量表
主键为AccID,YearID,uid
需要用A_Inc中的所有有差异的,Update A_Full表
比如:
对于uid=139的,f1列有变化,update它
对于uid=141的,cCustCode有变化,update它
Create Table A_Inc
(
AccID Varchar(10),
YearID Varchar(10),
uid int,
cCustCode Varchar(10),
cInvCode Varchar(10),
f1 decimal(16,2),
f2 decimal(16,2),
f3 decimal(16,2)
)Create Table A_Full
(
AccID Varchar(10),
YearID Varchar(10),
uid int,
cCustCode Varchar(10),
cInvCode Varchar(10),
f1 decimal(16,2),
f2 decimal(16,2),
f3 decimal(16,2)
)InSert Into A_Full Values ('041','2008',139,'00013','0419', 11.1, 66.6, 77.7)
InSert Into A_Full Values ('041','2008',140,'00013','0420', 55.6, 66.7, 77.8)
InSert Into A_Full Values ('041','2008',141,'00013','0421', 55.7, 66.8, 77.9)InSert Into A_Inc Values ('041','2008',139,'00013','0419', 55.5, 66.6, 77.7)
InSert Into A_Inc Values ('041','2008',140,'00013','0420', 55.6, 66.7, 77.8)
InSert Into A_Inc Values ('041','2008',141,'00014','0421', 55.7, 66.8, 77.9)因为有很多这样的表,而且表是不断增长的
请问怎样来做update是最有效率的,谢谢
我有两个表,结构一致
A_Inc表做为增量表
A_Full表做为全量表
主键为AccID,YearID,uid
需要用A_Inc中的所有有差异的,Update A_Full表
比如:
对于uid=139的,f1列有变化,update它
对于uid=141的,cCustCode有变化,update它
Create Table A_Inc
(
AccID Varchar(10),
YearID Varchar(10),
uid int,
cCustCode Varchar(10),
cInvCode Varchar(10),
f1 decimal(16,2),
f2 decimal(16,2),
f3 decimal(16,2)
)Create Table A_Full
(
AccID Varchar(10),
YearID Varchar(10),
uid int,
cCustCode Varchar(10),
cInvCode Varchar(10),
f1 decimal(16,2),
f2 decimal(16,2),
f3 decimal(16,2)
)InSert Into A_Full Values ('041','2008',139,'00013','0419', 11.1, 66.6, 77.7)
InSert Into A_Full Values ('041','2008',140,'00013','0420', 55.6, 66.7, 77.8)
InSert Into A_Full Values ('041','2008',141,'00013','0421', 55.7, 66.8, 77.9)InSert Into A_Inc Values ('041','2008',139,'00013','0419', 55.5, 66.6, 77.7)
InSert Into A_Inc Values ('041','2008',140,'00013','0420', 55.6, 66.7, 77.8)
InSert Into A_Inc Values ('041','2008',141,'00014','0421', 55.7, 66.8, 77.9)因为有很多这样的表,而且表是不断增长的
请问怎样来做update是最有效率的,谢谢
我是通过存储过程来统一调度及控制这些处理的,不希望用触发器想求一个优化Update语句谢谢了
Create Table A_Inc (
AccID Varchar(10),
YearID Varchar(10),
uid int,
cCustCode Varchar(10),
cInvCode Varchar(10),
f1 decimal(16,2),
f2 decimal(16,2),
f3 decimal(16,2)
)go
Create Table A_Full (
AccID Varchar(10),
YearID Varchar(10),
uid int,
cCustCode Varchar(10),
cInvCode Varchar(10),
f1 decimal(16,2),
f2 decimal(16,2),
f3 decimal(16,2)
)
go InSert Into A_Full Values ('041','2008',139,'00013','0419',11.1,66.6,77.7)
InSert Into A_Full Values ('041','2008',140,'00013','0420',55.6,66.7,77.8)
InSert Into A_Full Values ('041','2008',141,'00013','0421',55.7,66.8,77.9)InSert Into A_Inc Values ('041','2008',139,'00013','0419',55.5,66.6,77.7)
InSert Into A_Inc Values ('041','2008',140,'00013','0420',55.6,66.7,77.8)
InSert Into A_Inc Values ('041','2008',141,'00014','0421',55.7,66.8,77.9)
select * from A_Full
select * from A_Inc/*修改前
AccID YearID uid cCustCode cInvCode f1 f2 f3
---------- ---------- ----------- ---------- ---------- ------------------ ------------------ ------------------
041 2008 139 00013 0419 11.10 66.60 77.70
041 2008 140 00013 0420 55.60 66.70 77.80
041 2008 141 00013 0421 55.70 66.80 77.90(所影响的行数为 3 行)AccID YearID uid cCustCode cInvCode f1 f2 f3
---------- ---------- ----------- ---------- ---------- ------------------ ------------------ ------------------
041 2008 139 00013 0419 55.50 66.60 77.70
041 2008 140 00013 0420 55.60 66.70 77.80
041 2008 141 00014 0421 55.70 66.80 77.90(所影响的行数为 3 行)
*/update a
set cCustCode=b.cCustCode,
cInvCode=b.cInvCode,
f1=b.f1,f2=b.f2,f3=b.f3
from A_Full a inner join A_Inc b
on (a.accid=b.accid and a.yearid=b.yearid and a.uid=b.uid)
where (a.ccustcode<>b.ccustcode or a.cinvcode<>b.cinvcode or a.f1<>b.f1 or a.f2<>b.f2 or a.f3<>b.f3)
/*结果:
AccID YearID uid cCustCode cInvCode f1 f2 f3
---------- ---------- ----------- ---------- ---------- ------------------ ------------------ ------------------
041 2008 139 00013 0419 55.50 66.60 77.70
041 2008 140 00013 0420 55.60 66.70 77.80
041 2008 141 00014 0421 55.70 66.80 77.90(所影响的行数为 3 行)AccID YearID uid cCustCode cInvCode f1 f2 f3
---------- ---------- ----------- ---------- ---------- ------------------ ------------------ ------------------
041 2008 139 00013 0419 55.50 66.60 77.70
041 2008 140 00013 0420 55.60 66.70 77.80
041 2008 141 00014 0421 55.70 66.80 77.90(所影响的行数为 3 行)
*/
update a
set cCustcode=b.ccustcode,
cinvcode=b.cinvcode,
f1=b.f1,
f2=b.f2,
f3=b.f3
from a_full a,a_inc b
where a.accid=b.accid and a.yearid=b.yearid and a.uid=b.uid and (a.ccustcode<>b.ccustcode or a.cinvcode<>b.cinvcode
or a.f1<>b.f1 or a.f2<>f2 or a.f3<>f3)