Best way to duplicate almost whole database (same data for next period scenario)

Hi there
I'm working on a budgeting app which will be used to prepare a budget
for a given period. In the beginning of the next period all data from
the previous one should be duplicated and inserted into a new period so
they would become a base for preparing new data (by updating old
values).
I think first thing should be adding PERIOD column to each table. Or
not each one? For example it wouldn't be necessary to put this column
into intermediary tables, since period value for each row could be
identified by their parent tables. But I'm afraid it could make things
less transparent.
Also, I think that it's more convenient to have natural composite
primary keys for such use instead of single surrogates, since I could
just duplicate each row and just update PERIOD column which would be
part of a composite key (and composite foreign key, hence). But not
always natural keys are possible, so I will have to find a solution to
preserve relationships anyway.
Do you have any suggestions, or could you show me some resources on the
scenario? Should I (in one big transaction) turn off check constraints
(for tables where foreign keys cannot be null), duplicate all tables
with updating the PERIOD column and then update foreign keys basing on
data from previous period (ie. insert into current row's foreign key
column(s) the id of current version of row which was related to
previous version of the current row)?
thanks for any suggestion and sorry for not being too clear.
greets
hpHP
What is the version are you using? I think you need read about partition in
the SQL Server 2000/2005
"HP" <ha5en1@.gmail.com> wrote in message
news:1164677515.886061.267820@.l12g2000cwl.googlegroups.com...
> Hi there
> I'm working on a budgeting app which will be used to prepare a budget
> for a given period. In the beginning of the next period all data from
> the previous one should be duplicated and inserted into a new period so
> they would become a base for preparing new data (by updating old
> values).
> I think first thing should be adding PERIOD column to each table. Or
> not each one? For example it wouldn't be necessary to put this column
> into intermediary tables, since period value for each row could be
> identified by their parent tables. But I'm afraid it could make things
> less transparent.
>
> Also, I think that it's more convenient to have natural composite
> primary keys for such use instead of single surrogates, since I could
> just duplicate each row and just update PERIOD column which would be
> part of a composite key (and composite foreign key, hence). But not
> always natural keys are possible, so I will have to find a solution to
> preserve relationships anyway.
> Do you have any suggestions, or could you show me some resources on the
> scenario? Should I (in one big transaction) turn off check constraints
> (for tables where foreign keys cannot be null), duplicate all tables
> with updating the PERIOD column and then update foreign keys basing on
> data from previous period (ie. insert into current row's foreign key
> column(s) the id of current version of row which was related to
> previous version of the current row)?
> thanks for any suggestion and sorry for not being too clear.
> greets
> hp
>|||> What is the version are you using? I think you need read about partition in
> the SQL Server 2000/2005
it's 2k, sorry.
thanks for info, I'm reading about it at the moment. isn't it a
warehose solution? wouldn't it be an overkill for normal db where
performance isn't so important? or is there a way in which partitions
would make abovementioned operations more convenient?
thanks a lot
hp

Best way to duplicate almost whole database (same data for next period scenario)

Hi there
I'm working on a budgeting app which will be used to prepare a budget
for a given period. In the beginning of the next period all data from
the previous one should be duplicated and inserted into a new period so
they would become a base for preparing new data (by updating old
values).
I think first thing should be adding PERIOD column to each table. Or
not each one? For example it wouldn't be necessary to put this column
into intermediary tables, since period value for each row could be
identified by their parent tables. But I'm afraid it could make things
less transparent.
Also, I think that it's more convenient to have natural composite
primary keys for such use instead of single surrogates, since I could
just duplicate each row and just update PERIOD column which would be
part of a composite key (and composite foreign key, hence). But not
always natural keys are possible, so I will have to find a solution to
preserve relationships anyway.
Do you have any suggestions, or could you show me some resources on the
scenario? Should I (in one big transaction) turn off check constraints
(for tables where foreign keys cannot be null), duplicate all tables
with updating the PERIOD column and then update foreign keys basing on
data from previous period (ie. insert into current row's foreign key
column(s) the id of current version of row which was related to
previous version of the current row)?
thanks for any suggestion and sorry for not being too clear.
greets
hp
HP
What is the version are you using? I think you need read about partition in
the SQL Server 2000/2005
"HP" <ha5en1@.gmail.com> wrote in message
news:1164677515.886061.267820@.l12g2000cwl.googlegr oups.com...
> Hi there
> I'm working on a budgeting app which will be used to prepare a budget
> for a given period. In the beginning of the next period all data from
> the previous one should be duplicated and inserted into a new period so
> they would become a base for preparing new data (by updating old
> values).
> I think first thing should be adding PERIOD column to each table. Or
> not each one? For example it wouldn't be necessary to put this column
> into intermediary tables, since period value for each row could be
> identified by their parent tables. But I'm afraid it could make things
> less transparent.
>
> Also, I think that it's more convenient to have natural composite
> primary keys for such use instead of single surrogates, since I could
> just duplicate each row and just update PERIOD column which would be
> part of a composite key (and composite foreign key, hence). But not
> always natural keys are possible, so I will have to find a solution to
> preserve relationships anyway.
> Do you have any suggestions, or could you show me some resources on the
> scenario? Should I (in one big transaction) turn off check constraints
> (for tables where foreign keys cannot be null), duplicate all tables
> with updating the PERIOD column and then update foreign keys basing on
> data from previous period (ie. insert into current row's foreign key
> column(s) the id of current version of row which was related to
> previous version of the current row)?
> thanks for any suggestion and sorry for not being too clear.
> greets
> hp
>
|||
> What is the version are you using? I think you need read about partition in
> the SQL Server 2000/2005
it's 2k, sorry.
thanks for info, I'm reading about it at the moment. isn't it a
warehose solution? wouldn't it be an overkill for normal db where
performance isn't so important? or is there a way in which partitions
would make abovementioned operations more convenient?
thanks a lot
hp

Best way to duplicate almost whole database (same data for next period scenario)

Hi there
I'm working on a budgeting app which will be used to prepare a budget
for a given period. In the beginning of the next period all data from
the previous one should be duplicated and inserted into a new period so
they would become a base for preparing new data (by updating old
values).
I think first thing should be adding PERIOD column to each table. Or
not each one? For example it wouldn't be necessary to put this column
into intermediary tables, since period value for each row could be
identified by their parent tables. But I'm afraid it could make things
less transparent.
Also, I think that it's more convenient to have natural composite
primary keys for such use instead of single surrogates, since I could
just duplicate each row and just update PERIOD column which would be
part of a composite key (and composite foreign key, hence). But not
always natural keys are possible, so I will have to find a solution to
preserve relationships anyway.
Do you have any suggestions, or could you show me some resources on the
scenario? Should I (in one big transaction) turn off check constraints
(for tables where foreign keys cannot be null), duplicate all tables
with updating the PERIOD column and then update foreign keys basing on
data from previous period (ie. insert into current row's foreign key
column(s) the id of current version of row which was related to
previous version of the current row)?
thanks for any suggestion and sorry for not being too clear.
greets
hpHP
What is the version are you using? I think you need read about partition in
the SQL Server 2000/2005
"HP" <ha5en1@.gmail.com> wrote in message
news:1164677515.886061.267820@.l12g2000cwl.googlegroups.com...
> Hi there
> I'm working on a budgeting app which will be used to prepare a budget
> for a given period. In the beginning of the next period all data from
> the previous one should be duplicated and inserted into a new period so
> they would become a base for preparing new data (by updating old
> values).
> I think first thing should be adding PERIOD column to each table. Or
> not each one? For example it wouldn't be necessary to put this column
> into intermediary tables, since period value for each row could be
> identified by their parent tables. But I'm afraid it could make things
> less transparent.
>
> Also, I think that it's more convenient to have natural composite
> primary keys for such use instead of single surrogates, since I could
> just duplicate each row and just update PERIOD column which would be
> part of a composite key (and composite foreign key, hence). But not
> always natural keys are possible, so I will have to find a solution to
> preserve relationships anyway.
> Do you have any suggestions, or could you show me some resources on the
> scenario? Should I (in one big transaction) turn off check constraints
> (for tables where foreign keys cannot be null), duplicate all tables
> with updating the PERIOD column and then update foreign keys basing on
> data from previous period (ie. insert into current row's foreign key
> column(s) the id of current version of row which was related to
> previous version of the current row)?
> thanks for any suggestion and sorry for not being too clear.
> greets
> hp
>|||
> What is the version are you using? I think you need read about partition
in
> the SQL Server 2000/2005
it's 2k, sorry.
thanks for info, I'm reading about it at the moment. isn't it a
warehose solution? wouldn't it be an overkill for normal db where
performance isn't so important? or is there a way in which partitions
would make abovementioned operations more convenient?
thanks a lot
hp

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!