Copy sql server objects

Hello,I have two tables in my database that I need to copy all the data frominto a seperate database. Once data is copied, it is to be deletedfrom the source on a periodic basis. I thought the 'Copy SQL Serverobjects' DTS task would be useful, as it has 'append data' in the copyoption, but this fails upon running. It gives a primary key violation- does this mean the task is trying to add all data from the sourceinto the destination again? I thought 'append data' would allow it toonly add new rows, whilst ignoring the already copied ones.If I am over-simplifying it, please let me know, I need to find a wayto do this so the destination has all the rows from the beginning.This is all being done on SQL Server 2000 and we cannot move to 2005(just in case you recommend it!)Cheers,Max

Hi,
Import and export data utility can be used here. This utility gives you the option to drop and create objects and transfer data from source to destination.
You can select either replace existing data or append data where append data will not actually checks to insert new data. it will just retain previous rows and will insert new ones.
So in your case it would be better to go with replace existing data with drop and create objects
else build your own logic to check and insert new rows.

The only problem is that the data will be periodically removed from the source (the destination is an archive) - when I selected 'replace' instead of 'append', it deletes the data in the destination that's no longer in the source.
Any ideas?
Cheers,
Max

' else build your own logic to check and insert new rows.'
Yup, I think that's the best course of action.
Cheers

Hi,
As per your saying, if source data is removed then append data option shouldn't be a problem for fact data but I guess data in primary source table is in tact which will cause exception when tried to be appended on destination.
So you can keep append option only for fact tables and primary table entries can be built by replace existing option.

Hello,I have two tables in my database that I need to copy all the data frominto a seperate database. Once data is copied, it is to be deletedfrom the source on a periodic basis. I thought the 'Copy SQL Serverobjects' DTS task would be useful, as it has 'append data' in the copyoption, but this fails upon running. It gives a primary key violation ..............

For solving above problem you are requested to use the utility of Import and Export.
This will help you a lot. Regards