DTS - Check for the record existence before UPDATE/INSERT

Here is my DTS Package should look like,

1. Delete Staging file
On Success,
2. import EXCEL in to Staging file
On Success,
3. Update FACT File, if the record already exists.
On Success,
4. Insert records in to FACT file, if the record does not exists.

my question is, how can i check for the existence of record before UPDATE/INSERT??

FACT File does not have any primary key. but it has 4 foreign keys(CUST, COUNTRY, CURRENCY, CALENDAR).

Here is the Query i am using for INSERT.

INSERT INTO   CUSTOMER_FACT
(CUST_KEY,COUNTRY_KEY,CURRENCY_KEY,CALENDAR_KEY,  
GROSS_SALES_LOCAL, GROSS_SALES_US)
SELECT A.CUST_KEY,B.COUNTRY_KEY,C.CURRENCY_KEY,D.CALENDAR_KEY,  
E.GROSS_SALES_LOCAL, E.GROSS_SALES_LOCAL * C.RATE
FROM         CUSTOMER A, COUNTRY B, CURRENCY_CONVERSION_RATES C, CALENDAR_DATE D, CUSTOMER_STAGING E, customer_fact F
WHERE     A.CUST_NAME = E.CUST_NAME AND
B.COUNTRY_NAME = E.COUNTRY_NAME AND
B.COUNTRY_CURRENCY_CODE = C.FROM_CURRENCY_CODE AND
C.CURRENCY_CONVERSION_FISCAL_YEA = D.YEAR AND
C.CURRENCY_CONVERSION_MONTH = E.MONTH AND
C.CURRENCY_CONVERSION_TYPE  LIKE 'YTD'  AND
D.YEAR = E.YEAR AND
D.MONTH_DESC = E.MONTH_DESC and

F.cust_key <> A.cust_key and F.country_key <> B.country_key and F.currency_key <> C.currency_key.


But, this query is not writing anything. If i take out the customer_fact F then it is writing. But, the problem then is we can't check for the duplicates.
newcom408Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ABaruhConnect With a Mentor Commented:
use an Instead of Update or Instead of Insert trigger.

Take a look at Books Online if you don't know how to work with triggers.  You can use the "Inserted" keyword to work with the value to be inserted or updated
0
 
arbertCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.