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?
Solved! Go to Solution.
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.
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'.