I have a batch input system consisting of two tables which I've simplified below.



(Batch Table)
BatchID Int
BatchTotal Decimal(18.2)


(BatchTran Table)
BatchTranID Int
BatchTranHeaderID Int This links to BatchID
BatchTranValue Decimal(18.2)
BatchTranAccountNo Int




When the batch is complete I want to move the details from the Batch files and transfer them to the relevant Accounts files.




(Account Table)
AccountNoID Int
AccountTotal Decimal(18.2)


(AccountTran Table)
AccountTranBatchID Int
AccountTranBatchRef Int
AccountTranAmount Decimal(18.2)




I want to be able to run a stored procedure which selects all Batches and Transactions with a reference passed to the stored procedure (@BatchID) and create the entries in the AccountTran Table


    INSERT INTO AccountTran(AccountTranBatchID, AccountTranBatchRef, AccountTranAmount)
    SELECT BatchTranId, BatchTranHeaderID, BatchTranValue
    FROM BatchTran
    WHERE BatchTranHeaderID=@BatchID

No problems so far, the details are created perfectly.



What I want to do next is to take the values in BatchTranValue (which have been passed into AccountTranAmount) and add it to AccountTotal field

My question is Can this be completed in one stored procedure if so can you guide me as to how I achieve this?



Many thanks

__________________
Try our latest project www.nobanx.com Currency Exchange for members

Depends on what you mean.

If you are inserting a new row into Account Table you could simply do

INSERT INTO [Account Table](AccountTotal)
SELECT Sum(BatchTranValue) FROM BatchTran WHERE BatchTranHeaderID=@BatchID

Otherwise you could do something like:
DECLARE @Total decimal
SET @total = (SELECT AccountTotal from [Account Table] where AccountNoID = <value>

INSERT INTO [Account Table](AccountTotal)
SELECT Sum(BatchTranValue + @total) FROM BatchTran WHERE BatchTranHeaderID=@BatchID

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

The record in Account Table will already exist.

I want to add the value of the field AccountTran.AccountTranAmount to the value already held in Account.AccountTotal

I will then end up with a new record in AccountTran and an updated total in Account (Hopefully)

I've tried the following

UPDATE
        Account
    SET
        AccountTotal = AccountTotal + BatchTranValue
    FROM
        BatchTran AS BatchUpd
    JOIN
        Account AS AccountUpd
    ON
        AccountUpd.AccountRef = BatchUpd.BatchTranBatchID
    WHERE
        BatchTranBatchID=@BatchID


But it is only processing the first record in BatchTran
:(

I just can't see what it is I'm missing