>I don't see how any form of backup woyuld work, as the PKXFER and PKWarehouse will have the 2 additional columns in them. The only way I can see doing this is to SELECT INTO on each table.
>
>
>
>>Check http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b9474bb5-fa25-4a35-9da1-0e0a1f1eed07
>>
>>>>>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 to all tables in both databases.
>>>>>
>>>>>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?

Like Naomi mentions, situations like this is where SSIS excels. You may have to disable database constrains in your consolidated DB before the integration runs, but that is fairly trivial.