Hi,
I'm hoping someone can help. I wondering if its possible to get a sum of a column from a table you are inserting into... example pseudo code below
insert into A
Select
B.Id,
B.Type,
(Sum so far from A where A.Type = B.Type) as TotalSoFar
from B
In this way, as records are inserted i can get a total of a column for all the records already inserted with the same type...
Is this possible, and if not are there any workarounds that will achieve the same result?
thanks in advance...
why not ...
do this first
Select
B.Id,
B.Type,
(Sum so far from A where A.Type = B.Type) as TotalSoFar
then do the insert !!!
Not directly. You can't return a value to a variable and INSERT to a table at the same time.
If you're willing to INSERT the total to the table itself, then you could use an OUTPUT clause to "copy" the rows inserted to a temp table, then after the INSERT you could, of course, run whatever queries you want against that temp table.
Hi thanks for the fast replies...
the problem I have is im trying to allocate stock from one pot to another so needed to keep a count of how many i'ce used from each pot until its empty. My plan was to keep a count of how many i had already inserted from that pot (type) and take that off the total left to see if i need to move to a different pot (type). I wanted to find a way to do in set based, as the cursor that was already in the code is slow.