I have a scenario where i am moving 40 million records in to a table and then run a update script to update some records with the aggregate values example total count for each countries. Since i cant calculate these values in run time.(not the total of 40 million)
What is the best alternative solution rather than updating 40 million records.
Thanks in advance, Kannan
If you're calculating totals, why would you want to store them in the same table as the details?
To avoid updating those 40 million records, you could store the totals in a separate table, something like CountryTotal. You'd need two fields: CountryID and Total. Or better, three fields, add a primary key CountryTotalID as well.
This assumes your details table already has a CountryID. If that's not the case, you may want to consider remodeling a bit by creating a Country table that contains the list of countries. Your details table would then point to this new table.
answered Oct 05, 2012 at 12:47 PM