会用Insert TableName (字段1,字段2.....) select 字段1,字段2...... From tablenaem2 where 条件吗?
为什么用循环?想不通啊! insert table1 select * from table2 不就解决了吗?
declare cursor_insert cursor for select c# from cs declare @i int open cursor_insert fetch cursor_insert into @i while @@fetch_status=0 begin print @i fetch cursor_insert into @i end close cursor_insert deallocate cursor_insert
insert into ... select 是可以激活触发器的,你多虑了吧。不过不能撤销执行 insert into 查询的操作,请作好备份先
看下面的 if (select count(*) From TestTotal join inserted on tt_pro_no=TE_PRO_NO)>0 begin update testtotal set tt_total=tt_total+(select te_proqty from testTotal join inserted on testtotal.tt_pro_no=Inserted.TE_PRO_NO) where tt_pro_no=(select te_pro_No from testTotal join inserted on testtotal.tt_pro_no=Inserted.TE_PRO_NO) end else insert into testtotal(tt_pro_no,tt_total)select te_pro_no,te_proqty from inserted
lsp97(鵬) 不行,可能部分需要UPDATE,部分需要INSERT INTO 试试下面的 update table set tt_total = tt_total + b.tt_total from (select sum(tt_total)tt_total ,id from inserted group by id) b where table1.id=b.idinsert into table select a.* from inserted a left join table b on a.id=b.id where b.id is null
update testtotal set tt_total=tt_total+aa.te_proqty from inserted where testtotal.tt_pro_no=Inserted.TE_PRO_NO insert testtotal(tt_pro_no,tt_total) select te_pro_no,te_proqty from inserted where te_pro_no not in (select tt_pro_no from testtotal)
insert table1 select * from table2
不就解决了吗?
declare
@i int
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
print @i
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert
if (select count(*) From TestTotal join inserted on tt_pro_no=TE_PRO_NO)>0
begin
update testtotal set tt_total=tt_total+(select te_proqty from testTotal join inserted
on testtotal.tt_pro_no=Inserted.TE_PRO_NO)
where tt_pro_no=(select te_pro_No
from testTotal join inserted
on testtotal.tt_pro_no=Inserted.TE_PRO_NO)
end
else
insert into testtotal(tt_pro_no,tt_total)select te_pro_no,te_proqty from inserted
试试下面的
update table set tt_total = tt_total + b.tt_total
from (select sum(tt_total)tt_total
,id from inserted group by id) b
where table1.id=b.idinsert into table
select a.*
from inserted a left join table b on a.id=b.id
where b.id is null
看看这个!