how do i tell my dts package transform data task to overwrite already
existing records with new ones? I already truncated the table and am
inserting about 80000 records, but maybe 63 of them are duplicates. I get a
primary key contraint violation, but i need to have the primary key. On
resume next just skips the record, but i need it to overwrite the previously
inserted one.

I've looked all over for an answer but nothing relevant.... any ideas?

--
andrew
agale10495 (AT) aol (DOT) com

Oooh

The DataPump task is not really designed for this.

You can use Lookups. You would need to test for the existence of the PK and
if it existed then UPDATE all non PK attributes ELSE insert the new row.

This might be slow because Lookups are Row * Row

How large is the source dataset?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Andrew Gale" <AGale10495 (AT) aol (DOT) com> wrote

about 80,000 records. I was originally doing it the way you said, but the
package was taking 13 min.

I'm now using a bulk insert to a temp table, then using sql to compare and
make insert/updates between tables.

The package now takes about 3 or 4 minutes, so i'm happy.

Thanks for the info.....

--
andrew


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote