Hi Team,
I have requirement in Microsoft SQL
we are loading some data from upstreams systems to our db TransData by hourly basis. We load that data in to a new SourceData table. We send this Sourcedata table data to client.
In Sourcedata we are maintaining to two Id's columns one is auto generate id for Souredata and another is TransData Id column
Now client is asking to maintain same TransData Id column data in the Sourcedata table whenever new data is updated in the TransData for particular transaction
Example
First Load
TransData
Id Name Description
1 ABC ABC
SourceData
Id TransDataId Name Description
1 1 ABC ABC
Next Load
TransData -- Description column is updated
ID Name Description
2 ABC XYZ
SourceData- Truncate and load the data again
Id TransDataId Name Description
1 1 ABC XYZ
As per the above example Sourcedata table should maintain initial id of TransData
Please suggest how to generate this TransDataId in SourecData table remain same for each load in sourcedata.
let me know if you need any further information.
What I have tried:
I tried the
HashBytes('SHA2_256',TransId),2)
. It is generating an encrypted code. But client is not happy with this type of code