Suggest approach to replication strategy for reporting database SQL2000.

Hi All,

I've struggled to think of a sensible, descriptive title for this question but hopefully the following explanation will help!

I am using SQLServer 2000.

I have 2 Databases.
The 1st database (I call it ‘Staging’) contains about 30 tables.
The 2nd database (I call it ‘Reporting’) contains about 200 tables.

The 30 tables in Staging are identical in structure to their counterparts in Reporting.

My objective, on a routine basis (hourly would be fine), is to make the 30 tables in Reporting contain the same rows of data as in Staging.

The constraints on this are as follows:

1.      The Reporting tables must always contain data. The data can be out of date but the tables must never be empty.
2.      The Staging tables must be allowed to be updated, inserted, deleted and truncated as often as required.

I would like to know the best way to approach this issue. I have tried many approaches but all failed for one reason or another. I intentionally won’t list the approaches I have tried as it could be that my approach was correct but the implementation was wrong. I’d like this question to start from a clean sheet.

Thanks

Des