Click here to Skip to main content
15,878,231 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 7-Nov-23 20:41pm
v2
Comments
Maciej Los 8-Nov-23 2:48am    
Sorry, but i don't understand you...
If you want to keep original TransDataId, then change your SourceData table to be able save original data and increase internal Id (not TransDatId!).
[BTW]
Schema of tables would be helpful.
CHill60 8-Nov-23 3:28am    
Exactly how are you "loading" data and is the SourceData table really "new" each time?

1 solution

Well, the description of your issue is quite complicated, because you stated that you load incomming data into 2 tables:
1) firstly into TransData
2) then into SourceData

I guess that unique id for trans_data is added when data are loaded into TransData table. Whenever incomming data differs from existing (i.e.: description has been changed) TransDataId is changing (gets new id).

So, you have to change your database system to identify external and internal id of trans_data. You'll probably in need to add a LoadNumber to "see" how many times incomming data (with specific id) have been loaded into your system and what have been changed.
In other words, client have to use unique transid on his side. And you have to save it for further purposes... In your system, you have to add internal transid which has to be related with client transid.


Good luck!
 
Share this answer
 
v2
Comments
Andre Oosthuizen 9-Nov-23 13:59pm    
Good luck! - Well said and my 5, trying to connect loose ends to more loose ends ouch! Sorry to OP, I do not have a solution though...
Maciej Los 10-Nov-23 2:13am    
:) Thank you :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900