Re: Copy sql server objects

[Knowledgy]

The append is trying to pump source data into the target table that already
exists in that target table

look up outer joins in to find out how to add only data not already in your
target database. This pseudo code will give records in table2 that don't
exist in table1.

select colums
from table1
left outer join table2 on table1.id=table2.id
where table1.id is null

put the code in a sql task or in a proc stored in the db

--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/

Atlanta's Business Intelligence and Data Warehouse Experts

"MD" <mda...@medelect.co.uk> wrote in message
news:b03101c9-e0c4-48b3...@k2g2000hse.googlegroups.com...
> Hello,
> I have two tables in my database that I need to copy all the data from
> into a seperate database. Once data is copied, it is to be deleted
> from the source on a periodic basis. I thought the 'Copy SQL Server
> objects' DTS task would be useful, as it has 'append data' in the copy
> option, but this fails upon running. It gives a primary key violation
> - does this mean the task is trying to add all data from the source
> into the destination again? I thought 'append data' would allow it to
> only add new rows, whilst ignoring the already copied ones.
>
> If I am over-simplifying it, please let me know, I need to find a way
> to 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

[Ed Murphy]

Knowledgy wrote:

> The append is trying to pump source data into the target table that already
> exists in that target table
>
> look up outer joins in to find out how to add only data not already in your
> target database. This pseudo code will give records in table2 that don't
> exist in table1.
>
> select colums
> from table1
> left outer join table2 on table1.id=table2.id
> where table1.id is null

You need a right outer join for that. And it relies on table1.id
being set NOT NULL (which is probably true here, but still).

Having learned the following method, I now find it more intuitive:

select <list of columns>
from table2
where not exists (
select *
from table1
where table1.id = table2.id
)