Jobs, and RecordSets

[bgreenhouse]

Hi There

I'm trying to write a step in a job that is importing new data into a table. The job is mostly doing bulk inserts, but a bit of checking as well. We have a field in one table that is total sales for every customer. What I want to do is add the sum of the purchases in that update by each customer to their current total.

If I were to do this in ASP or VBScript, I'd simply return a recordset containing all the customer numbers in the data being imported, and then loop through it, for each customer number I'd get the sum of all purchases in the update, then add that sum to the customers previous total.

I'm not so sure how to do the stepping part in TSQL. In ASP I'm talking about:

Do While Not RS.eof
CustNum = RS.Fields("CustomerNumber")
sSQL = "Select Sum(Sale_Amt) as TotalSales from saletable where customernumber = " & CustNum
'then execute the SQL statement
'Then update the old one
sSQL = "Update CustomerTable Set Life_Sales = Life_Sales + TotalSales where customernumber = " & CustNum
'then execute that
RS.movenext
Wend (or whatever it is...)

Any suggestions as to how to achieve this in TSQL?

Ben

 

[tlbroadbent]

You don't need to step through anything. You need to process sets of data in SQL. Create a set of data (sub-query) containing the total sales per customer. Join that set to the set of customers in the customer table and update the Life_Sales column with total sales.

Update CustomerTable
Set Life_Sales = Life_Sales + TotalSales
From CustomerTable c Inner Join
(Select CustomerNumber, Sum(Sale_Amt) as TotalSales
From SaleTable Group By CustomerNumber) As s
On c.CustomerNumber=s.CustomerNumber

-----------------------------------
"The single biggest challenge to learning SQL programming is unlearning procedural programming." -Joe Celko Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bgreenhouse]

Thanks Terry!