Web Development

Ask something
Discussion Locked

Data Driven Query In SQL 2000

0 helpful
3 replies

by bgibilaro

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am working on a SQL 2000 DTS job that will look at a flat file, and then import the information from the flat file to our SQL DB. However, it must first check each record to see if it already exists. If it does, then it must update the record rather than insert a new one.

I am using a Data Driven Query for this. Here is the code for my ActiveX transformation script:

Function Main()
DTSDestination("hblID") = DTSSource("hblID")
DTSDestination("hblNum") = DTSSource("hblNum")
DTSDestination("mblNum") = DTSSource("mblNum")
DTSDestination("pol") = DTSSource("pol")
DTSDestination("pod") = DTSSource("pod")
DTSDestination("portLoadingDate") = DTSSource("portLoadingDate")
DTSDestination("estArrDate") = DTSSource("estArrDate")
DTSDestination("vessel") = DTSSource("vessel")
DTSDestination("voyage") = DTSSource("voyage")
DTSDestination("shipper") = DTSSource("shipper")
DTSDestination("consignee") = DTSSource("consignee")
DTSDestination("contLoadType") = DTSSource("contLoadType")
DTSDestination("contNum") = DTSSource("contNum")
DTSDestination("seal1") = DTSSource("seal1")
DTSDestination("cartons") = DTSSource("cartons")
DTSDestination("weight") = DTSSource("weight")
DTSDestination("cbm") = DTSSource("cbm")
DTSDestination("hsNum") = DTSSource("hsNum")
DTSDestination("description") = DTSSource("description")
DTSDestination("COL020") = DTSSource("Col020")

Select Case DTSSource("hblID")
Case DTSDestination("hblID") Main = DTSTransformstat_UpdateQuery
Case Else
Main = DTSTransformstat_InsertQuery
End Select
End Function

However, it never actually updates duplicates, it just inserts them again. I have noticed that if I put a hard code value intomy case statement, it works properly. So obviously, I am just not comparing properly.

Any help on this would be greatly appreciated. This is my first time doing a DDQ so I am a

3 replies

by matt

I'm not certain that I know the answer to your problem. However, looking at your Select Case statement - I think you're correct that you're not comparing properly.

You should have a WHERE statement to compare and find where there are empty records. If the records are empty or = 0, then InsertQuery, Else if the records are > 0 thenUpdateQuery. Just make sure you compare the destination record and not the source. Otherwise you'll always just insert the data.

This discussion is closed to new replies.