backup data for reporting

Question

What is the best method to transfer data from sales table to sales history table in sql server 2005. sales history table will be used for reporting.

Answer 1

Bulkcopy is fast and it will not use the transaction log. One batch run at the end of the day.

Deleting the copied records from your production server is a different situation that needs to be planed on that server's maintenance approach/plans. Your reporting server solution should not interfere with or affect the production server.

Keep in mind that your reporting server is not meant to be a backup of the data but rather a copy made exclusively for reporting purposes.

Also check on the server settings of your reporting server to be on Simple recovery model.

Answer 2

Most solutions will require 2 steps; -copy the records from source to target -delete records from source. It is essential that your source table have a primary key.

The "best" method depends on a lot of things.
How many records? Is this a production environment? What tools do you have?

Answer 3

Unless you are moving a large amount of data, a simple stored procedure should do the trick. A sql server job can manage the timing of when to call the proc.

Answer 4

Take a look at SSAS. OLAP is built for reporting and is easy to query with tools like excel pivot tables.