>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
There is some confusion in the terminology here - are you talking about different tables in different databases, 5 different tables in the same database?
Could you please re-state the problem?
If it's not broken, fix it until it is.
My Blog