title: How to convert a loop in SQL to Set-based logic
body: <p>I have spent a good portion of today and yesterday attempting to decide whether to utilize a loop or cursor in SQL or to figure out how to use set based logic to solve the problem.  I am not new to set logic, but this problem seems to be particularly complex.</p>
  
  <h2>The Problem</h2>
  
  <p>The idea is that if I have a list of all transactions (10's, 100's of millions) and a date they occurred, I can start combining some of that data into a daily totals table so that it is more rapidly view able by reporting and analytic systems.  The pseudocode for this is as such:  </p>
  
  <pre><code>foreach( row in transactions_table )
      if( row in totals_table already exists )
          update totals_table, add my totals to the totals row
      else
          insert into totals_table with my row as the base values
      delete ( or archive ) row
  </code></pre>
  
  <p>As you can tell, the block of the loop is relatively trivial to implement, and as is the cursor/looping iteration.  However, the execution time is quite slow and unwieldy and my question is: is there a non-iterative way to perform such a task, or is this one of the rare exceptions where I just have to "suck it up" and use a cursor?</p>
  
  <p>There have been a few discussions on the topic, some of which seem to be similar, but not usable due to the if/else statement and the operations on another table, for instance:</p>
  
  <p><a href="https://stackoverflow.com/questions/8979911/how-to-merge-rows-of-sql-data-on-column-based-logic">How to merge rows of SQL data on column-based logic?</a>  This question doesn't seem to be applicable because it simply returns a view of all sums, and doesn't actually make logical decisions about additions or updates to another table</p>
  
  <p><a href="https://stackoverflow.com/questions/557734/sql-looping">SQL Looping</a> seems to have a few ideas about selection with a couple of cases statements which seems possible, but there are two operations that I need done dependent upon the status of another table, so this solution does not seem to fit.</p>
  
  <p><a href="https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor">SQL Call Stored Procedure for each Row without using a cursor</a>  This solution seems to be the closest to what I need to do, in that it can handle arbitrary numbers of operations on each row, but there doesn't seem to be a consensus among that group.</p>
  
  <p>Any advice how to tackle this frustrating problem?</p>
  
  <h2>Notes</h2>
  
  <p>I am using SQL Server 2008</p>
  
  <p>The schema setup is as follows:</p>
  
  <p>Totals: (id int pk, totals_date date, store_id int fk, machine_id int fk, total_in, total_out)</p>
  
  <p>Transactions: (transaction_id int pk, transaction_date datetime, store_id int fk, machine_id int fk, transaction_type (IN or OUT), transaction_amount decimal)</p>
  
  <p>The totals should be computed by store, by machine, and by date, and should total all of the IN transactions into total_in and the OUT transactions into total_out.  The goal is to get a pseudo data cube going.</p>