Insert or Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert or Update

Author

Topic

jggtz
Starting Member

32 Posts

Posted - 2008-06-04 : 22:24:02

Scenario :

MS SQL 2005

Tables:
DETAIL --Here are all the detailed transactions
Customer
Item
Amount
...MoreFields

TOTAL --Here are the accumulated values (1 record by Customer-Item
Customer
Item
Accumulated
...MoreFields

What I want to know is the general logic to
-Read all records from table DETAIL
-If relationship Customer-Item exists in TOTAL table then UPDATE the TOTAL.Accumulated field adding the DETAIL.Amount field
-If relationship Customer-Item doesn't exists in TOTAL table then INSERT a new ecord with the values from DETAIL table

Because of my experience in VB and ADO my first try is to use a Cursor and loop record by record

But I know that is not the only and better solution

May You post some hint or some sample querie on how could be done

Thanks

JG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 23:51:53

You dont require to use a cursor here. You can apply a set based approach like :-

UPDATE t
SET t.Accumulated=d.Total
FROM TOTAL t
INNER JOIN (SELECT Customer,Item,SUM(Amount) AS Total
            FROM DETAIL
            GROUP BY Customer,Item)d

INSERT INTO TOTAL(Customer,Item,Accumulated)
SELECT d.Customer,d.Item,d.Total
FROM (SELECT Customer,Item,SUM(Amount) AS Total
            FROM DETAIL
            GROUP BY Customer,Item)d
LEFT JOIN TOTAL t
ON t.Customer=d.Customer
AND t.Item=d.Item
WHERE t.Customer IS NULL

WHat i've done here is to group the detail data based on Customer & Iem to get one line per combination and check if the combination exists. If exists, we update Accumulated with sum of amounts from detail rows. If not we will insert a new row with sum value.

jggtz
Starting Member

32 Posts

Posted - 2008-06-05 : 08:38:59

Thank You very much
I'll give it a try, but I know already that it'll work !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources