iSQLOutput - Update only Selected columns

Highlighted
Seven Stars

iSQLOutput - Update only Selected columns

My flow is simple and I am just reading a raw file into a SQL table.

 

At times the raw file contains data corresponding to existing records. I do not want to insert a new record in that case and would only want to update the existing record in the SQL table. The challenge is, there is a 'record creation date' column which I initialize at the time of record creation. The update operation overwrites that column too. I just want to avoid overwriting that column, while updating the other columns from the information coming from the raw file.

 

So far I am having no idea about how to do that. Could someone make a recommendation?


Accepted Solutions
Forteen Stars

Re: iSQLOutput - Update only Selected columns

what about Advanced Settings for Output component, "Use field options" then define Columns for Update 

?

-----------

View solution in original post


All Replies
Forteen Stars

Re: iSQLOutput - Update only Selected columns

what about Advanced Settings for Output component, "Use field options" then define Columns for Update 

?

-----------

View solution in original post

Seven Stars

Re: iSQLOutput - Update only Selected columns

Its a very simple "read raw file, push it to sql table" kind of flow. I've just updated the tSQLOutput component to prioritize update over insert, so that if a row with key exists, the record should be updated in the sql table. An insert operation would execute otherwise. This is the expected flow. Only challenge is that the update operation also overwrites exisiting records historical information (record_creation_date). I intend to keep the flow as is, just want to make the record_creation_date column immutable somehow.

 

Do note that its the same node that does the insert as well as update.

 

Screenshot from 2018-07-17 09-16-36.png

Seven Stars

Re: iSQLOutput - Update only Selected columns

Liked the recommendation given by vapukov. I defaulted the creation column to auto-populate in the SQL database itself. And I changed my flow to just update the remaining records. 


Yet another reminder of 'Simplification is underrated'. Smiley Happy

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog