求一个sqlserver存储过程，有两张表，一主一副，要求是把副表的数据插入主表，要根据部门号判断

Question

根据部门号判断
如果这条数据的部门号主表没有则插入，如果有则把两个表中的同一部门数据相加求总值更新到主表中

Answer 1

CREATE PROCEDURE [dbo].[up_UpdateDeptMain]

AS

INSERT Dept_Main
(Dept_Id,Total_Val)
SELECT distinct Dept_Id , 0 as Total_Val FROM Dept_Detail
where Dept_Id not In (select Dept_Id from Dept_Main) and Dept_Id <>''

update Dept_Main
set Total_Val = Total_Val + b.Sub_Total_Val
from Dept_Main a , (select Dept_Id , sum(Sub_Val) as Sub_Total_Val from Dept_Detail where Dept_Id <>'' group by Dept_Id ) b
where a.Dept_Id = b.Dept_Id