Solved

DTS - Check for the record existence before UPDATE/INSERT

Posted on 2004-09-08
2
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 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