可以不必理會是多筆還是單筆,統統一次把它做掉。這樣寫:--新增的狀況
INSERT INTO table1 (Col1,col2,col3)
SELECT a.*
FROM Inserted a LEFT JOIN table1 b
ON a.Col1=b.col1
WHERE b.Col1 IS NULL--修改的狀況
UPDATE table1
SET Col2=Col2+a.Col2
,Col3=Col3+a.Col3
FROM table1 INNER JOIN inserted a ON table1.Col1=a.Col1
INSERT INTO table1 (Col1,col2,col3)
SELECT a.*
FROM Inserted a LEFT JOIN table1 b
ON a.Col1=b.col1
WHERE b.Col1 IS NULL--修改的狀況
UPDATE table1
SET Col2=Col2+a.Col2
,Col3=Col3+a.Col3
FROM table1 INNER JOIN inserted a ON table1.Col1=a.Col1
FOR INSERT
AS
BEGIN
--汇总到临时表:
select col1,sum(col2) as col2,sum(col3) as col3 into #Temp from inserted group by col1--以下借用飘香兄的代码,但必须调换次序,否则会部分数据加倍--修改的狀況
UPDATE table1
SET Col2=Col2+a.Col2
,Col3=Col3+a.Col3
FROM table1 INNER JOIN #Temp a ON table1.Col1=a.Col1--新增的狀況
INSERT INTO table1 (Col1,col2,col3)
SELECT a.*
FROM #Temp a LEFT JOIN table1 b
ON a.Col1=b.col1
WHERE b.Col1 IS NULLEND
很不错,准备结贴。