My company has 5 locations. We are running a application called ProfitKey at all locations, and there is a PK database for each location, named PK1 through PK5. They all have the same structure. We want to snapshot the data from all locations into one warehouse database each night.

I'm thinking of this approach....

We would first create a database called PKXFER, and another called PKWarehouse, both with the same structure as the Profitkey databases, but I would add SiteKey and ArchiveDT columns.

Then each nite I would clear out the tables in the PKXFER database, append in a site's data, update the SiteKey and ArchiveDT columns, then append the data into the PKWarehouse.

1) Is there a better approach?

2) How would you append an entire DB into an another DB?

Thanks