Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcgants
Starting Member
2 Posts |
 Posted - 2008-02-26 : 06:28:59
|
| 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 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-02-26 : 08:10:57
|
| select * into newtable from oldtable |
 |
|
|
mcgants
Starting Member
2 Posts |
Posted - 2008-02-26 : 08:32:22
|
| The tables i'm dealing with are in excess of 10 million records, a 'select all where not in the other table' is a big expenditure I'm trying to get away from by using the DTS. |
|
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-02-26 : 08:46:56
|
| insert into destination (columns.....)select source.columns...... from source left join destination on source.primarykey=destination.primarykeywhere destination.primarykey is null"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
|
|
|
Copy sql server table data