Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
DerekDerek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP
10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexe
d
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
>|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60E
E-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:
>