DTS question-- overwriting existing records, keeping new ones -- sql server dts

Joseph Sack
12/21/2004 11:15:02 AM

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

[quoted text, click to view]

Jim Bancroft
12/21/2004 1:06:09 PM

Thanks guys--

Jim

Allan Mitchell
12/21/2004 6:46:29 PM

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

[quoted text, click to view]

Eshoo Isayo
12/27/2004 2:44:18 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : DTS question-- overwriting existing records, keeping new ones