Question

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

  • 1 March 2022
  • 6 replies
  • 334 views
R
Rank
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

 

1 Attachment

6 replies

Pradeep
Rank
Userlevel 3
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.

R
Rank
Userlevel 1

HI Pradeep,

Here I am sharing sample tables data.

Thank You. 

2 Attachments
R
Rank
Userlevel 1

attached the source and target table structures 

1 Attachment
Pradeep
Rank
Userlevel 3
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.
Drew Kreiger
Rank
Userlevel 5
Badge

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

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


Terms & Conditions