I am trying to set a DTS to update a remote server with
data from the main server. The table i m trying to update
has one primary key. I am not sure as to the best way to
get any records that are in table 1 into table 2. If i
use the Import Task from Enterprise Manager I just get an
error ' Violation of Primary Key Cannot insert duplicate
Key ' can anyone help as to where i am going wrong as i
know there are some new records in Table one that are not
in table 2

You can:

1. (Slow)

Build a package in designer

Building a Package in the DTS Designer
(http://www.sqldts.com/default.aspx?278)

Use lookups to compare each value in the Source with each in the destination
and only insert the new records

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)

2. (If the Source table is large then could be slow)

Export the whole of the table to the destination server. Export it to a
scratch table. You can then use TSQL statements to compare the PK values.

3.

Set up a liked server that links the Source to the destination. You can
then use a simple Query as the SourceSQLStatement for your datapump task OR
you could simply use an ExecuteSQL task to insert the new records.


--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Peter Newman" <pete (AT) cashbacs (DOT) co.uk> wrote