cancel
Showing results for 
Search instead for 
Did you mean: 

Insert/Update rows in the Target Table

Former Member
0 Kudos
519

<p>Hi,</p><p>Previously I was using DataStage as ETL tool. In DataStage, we can load the target table in the following way.</p><p>Update existing rows or insert new rows. </p><p>How can we implement the same in Data Integrator. I want to update the rows which are already existing in the target and insert the new records which are not available in the target.</p>

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

How do you update a table for rows that DO exist but not insert for source rows that DO NOT exist?

werner_daehn
Active Contributor
0 Kudos

For both cases you use the Table Comparison (TC) transform.

In TC you can specify what the input primary key is (the "where" clause so to speak), what column to compare etc. And it outputs the rows with an OPCODE of insert or update (or delete).

The table loader interprets that OPCODE and will create insert or update statements. If you want to filter records with of type Insert you use the Map_Operation for that: Map insert-to-discard.

https://wiki.sdn.sap.com:443/wiki/pages/viewpage.action?pageId=49414155

https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Loadingatablewithsurrogate+key

Former Member
0 Kudos

<p>On the target table, you will find the option &#39;Auto Correct&#39; load. This will do exactly what you describe :</p><p>Check is the row exists (based on primary key) : if YES update, if NO insert.</p><p>You can even specify which columns to use as primary key in stead of using the keys defined on the target. To do this set the &#39;Use input keys&#39; options. </p><p>You will find more details in the technical manuals... just search on Autocorrect Load.</p><p>-Ben.</p>

Former Member
0 Kudos

What if you have to compare the rows based on a non-primary key column.

-Bips