Insert/Update problem

[slugfest52]

I'm bringing data from a text file into a database table. I want to INSERT the new records and UPDATE any existing. The insertion works fine, but the update is not happening (no error, but the data is not updated). Is there anything wrong with this function?


Function Main()

' --- set up the columns to transform
DTSDestination("UID") = DTSSource("UID")
DTSDestination("Name") = DTSSource("Name")

If DTSLookups("UIDlookup").Execute(DTSSource("UID")) <> "" Then
' --- employee record exists, so update it
Main = DTSTransformstat_UpdateQuery
Else
' --- record doesn't exist, so insert it
Main = DTSTransformstat_InsertQuery
End If

End Function



Here's my Lookup:

SELECT UID
FROM TestData
WHERE (UID = ?)

 

[Narizz28]

Think about it for a second, you're asking the system for eveything with the UID = the passed arguement. The IF THEN is checking the result set for UID not equal to "" (aka nothing). Since a result set that has a match sometimes returns, the update works via the ELSE. But if the UID isn't in the system, your result set is empty, hence no records to have a UID <> to anything you provide. No records, no true condition, hence no insert.

Try this, have the lookup do a SELECT Count(UID)... and you IF THEN do IF lookup = 0 THEN. If no records, insert, else, there is a record(s), and thus update.