Solved

DTS - Check for the record existence before UPDATE/INSERT

Posted on 2004-09-08
2
394 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:newcom408
2 Comments
 
LVL 7

Accepted Solution

by:
ABaruh earned 250 total points
ID: 12007525
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
 
LVL 34

Expert Comment

by:arbert
ID: 12007530
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Task 11 43
how to make geography query faster?  SQL 7 44
sql server cross db update 2 20
SSRS Page Header from Group Data 2 23
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question