Hi there,

I keep trying to UPDATE rows on a existent table but I keep receive an
Error regarding it can't update

"Error at Destination for row 4289. Errors encountered so far in this
task: 1.
The statement has been terminated
Violation of PRIMARY KEY constraint 'PK_resColaboradores'. Cannot
insert duplicate key in object 'resColaboradores'"

I use: 'Append rows to destination table' and 'Enable identity insert'


table [res_Colaboradores] on Online SQL Server
idColaboradores [int] [primary key] [autonumber, 1, 1]
tName [char, 200]
tCategory [char, 50]
....

table [res_Colaboradores] on Local SQL Server
idColaboradores [int] [primary key] [autonumber, 1, 1]
tName [char, 200]
tCategory [char, 50]
....


Does anyone could help me on this, please... I've been like this for 6
month with no answer for my problem, I do the DELETE and INSERT... but
several tables have more than 70000 rows, and of course, it took a while...

Thank you in advance
Bruno Alexandre
(Sintra, PORTUGAL)

Are there rows in your source that have the same PK value as the rows
already in your destination ? Have you checked ? If there is then you
can't insert the same PK value.


--
--

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

"Bruno Alexandre" <bruno.pub (AT) filtrarte (DOT) pt> wrote

off course, that's why I wanted the append...

example:

in source there are :: row 1 | row 2 | row 3 | row 4 | row 5
in destination there is :: row 1 | row 2 | row 3

I want that the DTS only added the row 4 and row 5 to the destination, cause
there is already the previous rows.

isn't what 'Append rows to destination table' does? if not, what can I do to
make this work? imagine, all days delete 70000 rows and added again! it
takes ages... and there's a lot of tables with more that 50000 rows

Bruno Alexandre
(Sintra, PORTUGAL)



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> escreveu na mensagem
news:OMaoivcjDHA.2268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

You only want to append new rows. You can decide if they are new rows by
matching key values. If the key value is not in the destination then it is
a new row. If it is then it is either unchanged or updated. If there is a
row in the destination and not in the source then it is deleted.

You can accomplish the matching of key values and finding where holes exist
by using OUTER JOINS.


Append rows means append rows. It will add rows to the end from a source
table. There is no filter placed on the data i.e. "New records only please"



--
--

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

"Bruno Alexandre" <bruno.pub (AT) filtrarte (DOT) pt> wrote

I'm sorry, I thought append was like Access INSERT INTO... it only append
the records if the row not exist in Destination table.

by the way, isn't the explanation you gave the principle of Merge
Replication?

how can I do it with DTS, cause the SQL is in a shared server, and for that
they do not replicate databases.

thank you.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> escreveu na mensagem
news:uPsO7znjDHA.976 (AT) tk2msftngp13 (DOT) phx.gbl...

The way I would do it is to use the Data Driven Query task along iwth
triggers on my Source table. I can then trap actions on my Source table and
use the correct type of statement to move the rows(INSERT UPATE DELETE)



--
--

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

"Bruno Alexandre" <bruno.pub (AT) filtrarte (DOT) pt> wrote