How to generate a same UID for a row which will load multiple time and update with different data

Question

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 7-Nov-23 19:49pm

madhugut

Updated 7-Nov-23 20:41pm

Maciej Los

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2023-11-08T01:01:00

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!