• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

SQL 2000 DTS Export

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.
0
ajruiz
Asked:
ajruiz
  • 2
2 Solutions
 
sanjuskCommented:
why don't you define the package in which you first put the deletioin task and then the insertion task
0
 
MichaelSFullerCommented:
1) Add a SQL coonection to DTS package name it 'Source
2) Add another SQL connection name it Destination
3) Add an execute SQL task that truncates the table in the destination-using either the truncate table method, or delete from
4)Add a transform data tyask between the source and desitination
5) Add a workflow from theexecute sql task to the transform data task on success
6) save
7) execute the pacakge
8) done
0
 
sanjuskCommented:
correct , MichealsFuller is step of what I was asking you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now