SQL 2000 DTS Export
Posted on 2005-02-26
I am wanting to export data from a production database to testing database on the same server replacing the data not appending it. Starting from the Production database I export and the wizard walks me through it. I choose the source with sql authentication using the user production (owner of the tables) then choose my destination with sql authentication using the user testing (owner of the table). I did it this way to maintain table ownership. I then Copy tables and views from source database and choose the tables I want to copy. Unfortunately this doesn't replace the data only appends it. I tried the third option which gives me the ability to indicate replace the data but the ownership doesn't get preserved.
What is the best method to replace data on another database while preserving the original owner.