update b
set b.hitnum=b.hitnum+1
from a
where a.a1=b.b1 and a.a2=b.b2insert b select a1,a2,1
from a
where not exists(
select 1 from b where b.b1=a.a1 and b.b2=a.a2)
set b.hitnum=b.hitnum+1
from a
where a.a1=b.b1 and a.a2=b.b2insert b select a1,a2,1
from a
where not exists(
select 1 from b where b.b1=a.a1 and b.b2=a.a2)
go
create table a([a1] int,[a2] int)
insert a select 1,2
union all select 1,2
union all select 1,3
union all select 2,2
union all select 2,2
union all select 3,3if object_id('[b]') is not null drop table [b]
go
create table [b]([b1] int,[b2] int,[hitnum] int)
insert [b] select 1,2,2
union all select 1,3,1
union all select 2,2,2
union all select 3,3,1
if object_id('tg_a') is not null drop trigger tg_a
go
create trigger tg_a on a
for insert
as
update b
set b.hitnum=b.hitnum+1
from inserted
where inserted.a1=b.b1 and inserted.a2=b.b2
goselect * from b
/*
b1 b2 hitnum
----------- ----------- -----------
1 2 NULL
1 3 NULL
2 2 NULL
3 3 NULL(4 行受影响)
*/insert a select 2,2select * from b
/*
b1 b2 hitnum
----------- ----------- -----------
1 2 2
1 3 1
2 2 3
3 3 1(4 行受影响)
*/这是插入的例子,看看是不是要像这样的.
b1 b2 hitnum
----------- ----------- -----------
1 2 2
1 3 1
2 2 2
3 3 1(4 行受影响)--插入后:b1 b2 hitnum
----------- ----------- -----------
1 2 2
1 3 1
2 2 3
3 3 1(4 行受影响)
我现在是用group来弄,但是效率肯定不高
1. 做好A表和B表的备份;
2. 数据库在做这项操作的时候,最好能够不被其他应用使用。