I'm fairly sure this question falls in the "DTS 101" category, so forgive me
if it's as basic as they come....I have a source and destination table, each
with the same schema. I'd like to copy the contents of the source into the
destination, overwriting those destination records with matching primary
keys but keeping the ones unique to the destination. How can I best do
this?

Same DB ?

UPDATE T1
SET T1.col1 = T2.col1.....................
FROM Table1 T1 JOIN Table2 T2
ON T1.PKCol = T2.PkCol

INSERT Table1(col list.........................)
SELECT T2.collist
FROM Table2 T2 LEFT OUTER JOIN Table1 T1
ON T2.PKCol = T1.PkCol
WHERE T1.PkCol IS NULL




--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Jim Bancroft" <asdfsklw (AT) nowhere (DOT) com> wrote

Hi Jim,

For this I do the following:

1. Import the data to a staging/work table using a transformation task
(truncating the staging table first).

2. Using an 'Execute SQL' task to insert/update records in the destination
based on new records in the staging table.

You could potentially use the Data Driven Query task for all of this
instead, but that task is not very user-friendly (and probably will not
perform as well on large result sets).

Best Regards,

Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com


"Jim Bancroft" wrote:

Here's something I use.
The _Container table gets flushed, then a text file is imported into it.
DeliveryNum and DeliveryLineNum are primary keys in the permanent table.

/*Update prev recs*/
UPDATE tblFreightCharge
SET tblFreightCharge.OrderNum = [tblFreightCharge_Container].[OrderNum],
tblFreightCharge.SKU_Num = [tblFreightCharge_Container].[SKU_num],
tblFreightCharge.OrderDate =
[tblFreightCharge_Container].[OrderDate],
tblFreightCharge.ShipDate = [tblFreightCharge_Container].[ShipDate],
tblFreightCharge.BU_Code = [tblFreightCharge_Container].[BU_Code],
tblFreightCharge.Quantity = [tblFreightCharge_Container].[Quantity],
tblFreightCharge.FreightCharge =
[tblFreightCharge_Container].[FreightCharge],
tblFreightCharge.TrackingNum =
[tblFreightCharge_Container].[TrackingNum],
tblFreightCharge.CarrierSvcLvl =
[tblFreightCharge_Container].[CarrierSvcLvl]
FROM tblFreightCharge_Container INNER JOIN tblFreightCharge
ON (tblFreightCharge_Container.DeliveryNum = tblFreightCharge.DeliveryNum)
AND (tblFreightCharge_Container.DeliveryLineNum =
tblFreightCharge.DeliveryLineNum);


/*Append new recs*/
INSERT INTO tblFreightCharge ( DeliveryNum, DeliveryLineNum, OrderNum,
SKU_Num, OrderDate, ShipDate, BU_Code, Quantity, FreightCharge, TrackingNum,
CarrierSvcLvl )
SELECT tblFreightCharge_Container.*
FROM tblFreightCharge_Container LEFT JOIN tblFreightCharge
ON (tblFreightCharge_Container.DeliveryLineNum =
tblFreightCharge.DeliveryLineNum)
AND (tblFreightCharge_Container.DeliveryNum = tblFreightCharge.DeliveryNum)
WHERE (tblFreightCharge.DeliveryNum Is Null) AND
(tblFreightCharge.DeliveryLineNum Is Null);


Eshoo

"Jim Bancroft" <asdfsklw (AT) nowhere (DOT) com> wrote