Hi,

I have set up DTS to import a csv file into a scratch table. From here I
have another connection to the final destination table.

But before going to the Final Table I have added a DDQ to check for insert
or update depending on wether the record exists.

Text File ------> Scratch Table ------> Final Table

I have the DDQ updating if record in final table exists and inserting if
not. At least it is suppose to.

Here is my query I have in the "Queries" tab for the "Update" of the DDQ
properties...


---start code---
UPDATE tblContacts
SET CONT_ID = ?, FIRSTNAME = ?, LASTNAME = ?, EMAIL_ADR = ?
WHERE CONT_ID = ? IF EXISTS

(SELECT *
FROM tblContactsScratch
WHERE CONT_ID = ?)

UPDATE tblContacts
SET CONT_ID = ?, FIRSTNAME = ?, LASTNAME = ?, EMAIL_ADR = ?

WHERE (CONT_ID = ?)

ELSE

INSERT INTO
tblContacts(CONT_ID, FIRSTNAME, LASTNAME, EMAIL_ADR)
VALUES (?, ?, ?, ?)
---end code---

Here is what my transformation looks like.


---startcode---
'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
' the following will be used as parameters in place of ?
DTSDestination("CONT_ID") = DTSSource("CONT_ID")
DTSDestination("FIRSTNAME") = DTSSource("FIRSTNAME")
DTSDestination("LASTNAME") = DTSSource("LASTNAME")
DTSDestination("EMAIL_ADR") = DTSSource("EMAIL_ADR")
Main = DTSTransformstat_UpdateQuery
End Function
---endcode---


Here is my problem... When the package executes it does fine. but it is
adding/inserting duplicate records in the final table. I only want it to
update those records and insert if a record does not exists.

Is this the right approach? It seems like it should work as intended but
just inserts records when it shoul dbe updating. I am at a loss on this.
Any help would be much appreciated.

--
Joey Durham
www.ultraweaver.com - Tutorials/Downloads
www.geekforum.com

Maybe it is just the SQL that seems to be out of wack? Does this SQL
statement look right? It is in the "Update" section of my DDQ. It should
update the field in tblContacts if they exist and insert a if it does not.


UPDATE tblContacts
SET CONT_ID = ?, FIRSTNAME = ?, LASTNAME = ?, EMAIL_ADR = ?
WHERE CONT_ID = ? IF EXISTS

(SELECT *
FROM tblContactsScratch
WHERE CONT_ID = ?)

UPDATE tblContacts
SET CONT_ID = ?, FIRSTNAME = ?, LASTNAME = ?, EMAIL_ADR = ?

WHERE (CONT_ID = ?)

ELSE

INSERT INTO
tblContacts(CONT_ID, FIRSTNAME, LASTNAME, EMAIL_ADR)
VALUES (?, ?, ?, ?)

--
Joey Durham
www.ultraweaver.com - Tutorials/Downloads
www.geekforum.com



"Joey D" <joey (AT) ultraweaver (DOT) com> wrote

You would normally only have the UPDATE statement in the "Update" query
for your DDQ. You would have a single INSERT in the "Insert" query. You
use a lookup to do the SELECT for existence check. You then have an If
construct in the script, something like this-

Function Main()
' the following will be used as parameters in place of
?
DTSDestination("CONT_ID") = DTSSource("CONT_ID")
DTSDestination("FIRSTNAME") = DTSSource("FIRSTNAME")
DTSDestination("LASTNAME") = DTSSource("LASTNAME")
DTSDestination("EMAIL_ADR") = DTSSource("EMAIL_ADR")

If DTSLookups("CheckExistence").Value = 1 Then
Main = DTSTransformstat_UpdateQuery
Else
Main = DTSTransformstat_InsertQuery
End If
End Function


The return value from the transformation function determines which query
is run. Of course you can even assign different values to each query,
and the query purpose doesn't have to match the nominal names either,
but they fit with the classic usage.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thank you Darren!

I originally tried this (new to SQL and DTS) and did not get it to work. I
will try again based on the info. BTW, thanks for the site. Has helped me
understand DTS a bit better.

--
Joey Durham
www.ultraweaver.com - Tutorials/Downloads
www.geekforum.com

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote