还是分两步走比较好.1.找存在的.直接UPDATE 2.不存在的.insertif exists(select 1 from tb where ...) update ... else insert ...
我这样写的,测试了下,可以用,但感觉好业余 ALTER trigger [dbo].[trig_insert_or_update_day_pv] ON [dbo].[DAY_PV] instead of insert as begin
update D_PV SET D_PV.PV=INS.PV FROM dbo.DAY_PV AS D_PV INNER JOIN INSERTED AS INS ON D_PV.DAY_NAME=INS.DAY_NAME AND D_PV.UrlorWebsiteKey=INS.UrlorWebsiteKey insert [dbo].[DAY_PV] select * from inserted ins where not exists (select 1 from [dbo].[DAY_PV] where Day_name=ins.Day_name and UrlorWebsiteKey= ins.UrlorWebsiteKey )
先update,返回行数为0时insert
if exists(select 1 from tb where ...) update ... else insert ...
#1.08及以上直接用merger语法 #2.08以下,如果不考虑并发,用 if exists() 判断即可 #3.08以下,考虑并发的话,得用事务并加锁处理
2.不存在的.insertif exists(select 1 from tb where ...)
update ...
else
insert ...
我这样写的,测试了下,可以用,但感觉好业余
ALTER trigger [dbo].[trig_insert_or_update_day_pv] ON [dbo].[DAY_PV]
instead of insert
as
begin
update D_PV
SET D_PV.PV=INS.PV FROM
dbo.DAY_PV AS D_PV INNER JOIN INSERTED AS INS
ON D_PV.DAY_NAME=INS.DAY_NAME AND D_PV.UrlorWebsiteKey=INS.UrlorWebsiteKey
insert [dbo].[DAY_PV]
select * from
inserted ins where not exists (select 1 from [dbo].[DAY_PV]
where Day_name=ins.Day_name and UrlorWebsiteKey= ins.UrlorWebsiteKey
)
update ...
else
insert ...
#2.08以下,如果不考虑并发,用 if exists() 判断即可
#3.08以下,考虑并发的话,得用事务并加锁处理