Best Way To Aggregate a Large Table

I have a table that looks like this (simplified)
Account Period Amount
Assets 1 100
Assets 2 10
Assets 3 0
I want to create a new table to carry a balance for the amount column.
So that Period 1 = Period 1, Period 2 = Period 1 + Period 2, etc. Like
this:
Account Period Amount
Assets 1 100
Assets 2 110
Assets 3 110
The real table has 140 million rows and 25 columns. I can make this
work by doing 12 Insert statement in my new table but I'm thinking
there has to be a faster and more efficient way. TIA for any ideas.Try:
insert OtherTable
select
m.Account
, m.Period
, (select sum (i.Amount) from MyTable i
where i.Account = m.Account
and i.Period <= m.Period)
from
MyTable m
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ctal" <witp_turns@.yahoo.com> wrote in message
news:1150927173.463636.315330@.c74g2000cwc.googlegroups.com...
I have a table that looks like this (simplified)
Account Period Amount
Assets 1 100
Assets 2 10
Assets 3 0
I want to create a new table to carry a balance for the amount column.
So that Period 1 = Period 1, Period 2 = Period 1 + Period 2, etc. Like
this:
Account Period Amount
Assets 1 100
Assets 2 110
Assets 3 110
The real table has 140 million rows and 25 columns. I can make this
work by doing 12 Insert statement in my new table but I'm thinking
there has to be a faster and more efficient way. TIA for any ideas.|||Not knowing the purpose, but it seems as though a VIEW may be a better
option. (Using Tom's query .) Storing calculated values seems a bit
'risky' -as well as unnecessary.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23N75rBYlGHA.3304@.TK2MSFTNGP03.phx.gbl...
> Try:
> insert OtherTable
> select
> m.Account
> , m.Period
> , (select sum (i.Amount) from MyTable i
> where i.Account = m.Account
> and i.Period <= m.Period)
> from
> MyTable m
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ctal" <witp_turns@.yahoo.com> wrote in message
> news:1150927173.463636.315330@.c74g2000cwc.googlegroups.com...
> I have a table that looks like this (simplified)
> Account Period Amount
> Assets 1 100
> Assets 2 10
> Assets 3 0
> I want to create a new table to carry a balance for the amount column.
> So that Period 1 = Period 1, Period 2 = Period 1 + Period 2, etc. Like
> this:
> Account Period Amount
> Assets 1 100
> Assets 2 110
> Assets 3 110
>
> The real table has 140 million rows and 25 columns. I can make this
> work by doing 12 Insert statement in my new table but I'm thinking
> there has to be a faster and more efficient way. TIA for any ideas.
>|||Arnie Rowland wrote:
> Not knowing the purpose, but it seems as though a VIEW may be a better
> option. (Using Tom's query .) Storing calculated values seems a bit
> 'risky' -as well as unnecessary.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
I agree, but this is that unusual case where I need a real table
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23N75rBYlGHA.3304@.TK2MSFTNGP03.phx.gbl...
The query worked wonderfully, thanks Tom!