A,B两个表,B表数据动态更新,检查B表中的数据是否存在A表,如果不存在则插入,如果存在则检查对应字段值大小,小则忽略,大则更新,谢谢表A,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,12,13,23
3 ,5 ,16,34,45
表B,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,11,23,100
5 ,5 ,16,34,45
想处理B表得到A表数据为
表A,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,12,23,100
3 ,5 ,16,34,45
5 ,5 ,16,34,45
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,12,13,23
3 ,5 ,16,34,45
表B,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,11,23,100
5 ,5 ,16,34,45
想处理B表得到A表数据为
表A,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,12,23,100
3 ,5 ,16,34,45
5 ,5 ,16,34,45
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (xh int,zz int,a1 int,a2 int,a3 int)
insert into #A
select 1,5,12,13,23 union all
select 3,5,16,34,45
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (xh int,zz int,a1 int,a2 int,a3 int)
insert into #B
select 1,5,11,23,100 union all
select 5,5,16,34,45insert #A select * from #B where not exists (select 1 from #A where xh=#B.xh and zz=#B.zz)select * from #A/*
xh zz a1 a2 a3
----------- ----------- ----------- ----------- -----------
1 5 12 13 23
3 5 16 34 45
5 5 16 34 45
*/
A,B两个表,B表数据动态更新,检查B表中的数据是否存在A表,
如果不存在则插入,如果存在则检查对应字段值大小,小则忽略,大则更新,谢谢
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,12,13,23
3 ,5 ,16,34,45
表B,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,11,23,100
5 ,5 ,16,34,45
想处理B表得到A表数据为
表A,
xh(主键1),zz(主键2),a1,a2,a3
1 ,5 ,12,23,100
3 ,5 ,16,34,45
5 ,5 ,16,34,45
*/
set nocount on
if object_id('ta') is not null drop table ta
if object_id('tb') is not null drop table tb
create table ta (xh int ,zz int, a1 int, a2 int, a3 int ,constraint pk_ta primary key (xh,zz))
create table tb (xh int ,zz int, a1 int, a2 int, a3 int ,constraint pk_tb primary key (xh,zz))
go
insert into ta select 1,5,12,13,23
union all select 3,5,16,34,45
go
create trigger t_autoupdate on tb for update,insert
as
set nocount on
update a set a.a1=i.a1,a.a2=i.a2,a.a3=i.a3
from ta a inner join inserted i on a.xh=i.xh and a.zz=i.zz where (a.a1+a.a2+a.a3)<(i.a1+i.a2+i.a3)
insert into ta select * from inserted i where not exists(select 1 from ta a where a.xh=i.xh and a.zz=i.zz)
set nocount off
go
insert into tb values(1,5,11,23,100)--大则更新
insert into tb values(5,5,16,34,45)--不存在则插入
select * from ta
go
truncate table tb
go
insert into tb values(1,5,12,12,12)--小则忽略
insert into tb values(6,5,16,34,45)--不存在则插入
select * from ta
go
set nocount off
select * from ta
go
update tb set a3=1000 where xh=1 and zz=5
select * from ta
update的