Update Insert Problem

Question

Update Insert Problem

msutter

SSC Eights!

Points: 990
More actions
February 2, 2005 at 9:45 am

#65344

Help -
I have a classic case of a match merge. I have a table representing input that I have to check against another table in the same database. If a matching record exists I update using a lookup if not I insert using the transformation. I have done this successfully with the input coming from a DB2 connection.
With both coming from a SQL Server db the transformation loops indefinitely.
SourceRecordExists Lookup =
SELECT     SOCODE
FROM         CS_SOURCE
WHERE     (SOAREA = 'AC') AND (SOCODE = ?)
Update Lookup =
UPDATE    CS_SOURCE
SET              SOLEVEL = ?, SODESC = ?
WHERE     (SOAREA = 'AC') AND (SOCODE = ?)
GetMinimumLevel Lookup =
SELECT     MIN(SOLEVEL) AS Expr1
FROM         CS_SOURCE_DEX
WHERE     (SOAREA = 'AC')
Transformation Logic
Function Main()
Dim iMinLevel
Dim iLevelNo
Dim sLevel
Dim sCodeFound
Dim lRecsAffected

iMinLevel = DTSLookups("GetMinimumLevel").Execute(DTSGlobalVariables("$ORG").Value)
iLevelNo = CInt(DTSSource("SOLEVEL")) - iMinLevel + 1
sLevel = "L" & CStr(iLevelNo)
sCodeFound = DTSLookups("SourceRecExists").Execute(DTSSource("SOCODE"))
If sCodeFound = "" Then    'Not found - insert the record
  DTSDestination("SOAREA") = "AC"
  DTSDestination("SOCODE") = DTSSource("SOCODE")
  DTSDestination("SOLEVEL") = sLevel
  DTSDestination("SODESC") = DTSSource("SODESC")
  Main = DTSTransformStat_OK
Else
  lRecsAffected = DTSLookups("Update").Execute(sLevel, DTSSource("SODESC"), DTSSource("SOCODE"))
  Main = DTSTransformStat_SkipInsert

End If
End Function

Does anyone know of any restrictions here or if there is an alternate approach??

TIA for any ideas

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

The DTS Transformation you're doing is processing the input a single row at a time. Are the two tables accessible from one server? ie: can you run a single query across both tables

If so, replace your DTS Transformation with good old set-based T-SQL statements.

If not, use a straight Datapump to bring the data across to a staging table and then use T-SQL from there.

--------------------
Colt 45 - the original point and click interface

msutter SSC Eights! Points: 990 More actions · Answer 2

Thanks for the reply. Yes both tables are in the same database.

I haven't ever built sql statements to accomplish this, can you supply an example?

Thanks again

philcart SSC-Forever Points: 47796 More actions · Answer 3

Super simple really. just use a standard T-SQL UPDATE for matching records and an INSERT for missing records,

eg:

UPDATE Tbl1

SET Tbl1.Field1 = Tbl2.Field1

FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.Key = Tbl2.Key

INSERT INTO Tbl1 ( Field1 )

SELECT Tbl2.Field1

FROM Tbl2 LEFT JOIN Tbl1 ON Tbl1.Key = Tbl2.Key

WHERE Tbl1.Key IS NULL

These statements would execute in there own ExecuteSQL tasks. Probably even better if you create them as stored procedures and then call them in the ExecuteSQL task instead.

--------------------
Colt 45 - the original point and click interface

msutter SSC Eights! Points: 990 More actions · Answer 4

msutter

SSC Eights!

Points: 990

February 4, 2005 at 6:53 am

#540669

Got it --

Thanks very much.