Insert/Update rows in the Target Table

[Former Member]

<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>

[Former Member]

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

[werner_daehn]

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]

<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]

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

-Bips