Table1 has coulmns of CodeId, TypeId and ModelId, Num1, Num2 ... the Num1, Num2 have data type of int values and the combination of CodeId, TypeId and ModelId can't be the same. In other word, those CodeId, TypeId and ModelId combination must be unique. If any same combination ID found then add up Num1 and Num2 and save the sum as record then old records must be removed. See following sample:

From above table Rec# 1 and 3 have same ID combination. So Rec#1 and 3 must be removed and a new rec with same ID combination and Num1=53, Num2=83 should be added before removal. New rec looks like this:

-- Insert into Temp table
Select CodeId,TypeId,ModelId,SUM(Num1)NUM1,SUM,(Num2)NUM2........
into #temp
from Table
Group by CodeId,TypeId,ModelId
-- Delete the entries
Delete P from
(
Select CodeId,TypeId,ModelId,ROW_NUMBER() OVER (PARTITION BY CodeId,TypeId,ModelId Order by CodeId) as Seq
from Table
)P
Where P.Seq > 1
-- Reinsert back
Insert into table
Select CodeId,TypeId,ModelId,NUM1,NUM.....from #temp

Thanks sodeep. I think the 2nd part of deletion, the P.Seq should be greater than 0 not 1. If P.Seq > 1 it only deletes the duplicated records not all. Later when it comes to 3rd part, write back the #temp table it should write to an empty table.I am thinking about if it's possible that getting data for only duplicated records in 1st part because the table is huge, about millions records there.Anyway thanks a lot again.

Thanks guys. jim, the select distinct part adds all the columns, even the ones without duplicated.sodeep, this time it works perfect!!! That Having Count(*) > 1 clears up the puzzle and deletion hits on only the duplicated combination. One more question: I use this in a store procedure to insert a new record to table, my question is should I embed the code into the stored procedure at the end or put it into the trigger of table? which one gives the best performance?Thank you guys again!!!