Question

How to perform an update sql query on a destination table (JDBC Destination Oracle)

  • 1 March 2022
  • 6 replies
  • 113 views

Userlevel 1

Hi, 

I am trying to replicate an existing Informatica Mapping into Streamsets.

I selected JDBC Consumer to compare staging table with target table for identifying and separating the records for update and insert into target table.

The existing mapping is using an Update Strategy Transformation (Informatica) to perform an update on Target Table based upon the received update records.

I am trying to achieve the above functionality in StreamSets to perform an update on target table.

Please share your inputs.

Attached screen shot for reference.

Regards,

Raghuram

 


6 replies

Userlevel 2
Badge +1

@raghuramxlsoftek Can you share some sample data from source DB and how are you planning to select records for Insert vs Update. This would help us give the appropriate recommendation.

Userlevel 1

HI Pradeep,

Here I am sharing sample tables data.

Thank You. 

Userlevel 1

attached the source and target table structures 

Userlevel 2
Badge +1

@raghuramxlsoftek

  1. If you are already aware which records need to be updated or deleted in target, shared pipeline snapshot looks fine to me. Since am not sure of the stage configurations or schema of the data can say as an idea it should work fine. 
  2. If unaware of the records to be updated or deleted, would suggest you to try JDBC lookup on target table > compare it with source stage records and accordingly use Stream Selector to update or delete in destination.
Userlevel 5
Badge

@Vimalathithan I have moved your question to a new topic. This will allow a better experience all around. 

Userlevel 1

Hi Pradeep,

Thanks for your inputs.

On further analysis, we observed that target table does have PK column.

Hence, we are working on to alter the table to include PK column.

Hope this resolves our update problem as per your Point 1 mentioned above.

Thank You.

   

 

 

Reply