Options
Random row selection in table to table synchronization.
devi_sree
Posts: 3 New member
I have a requirement where the data in one table need to be updated with corresponding data in another table. There is a join column as well which I can use. But the issue is, I need the update to be random.
For eg:
In table1 there are 3 columns:
name1,acctnum1,acctType1
Another table2:
acctType2, acctnum2
Here the acctType column will be join column and there can be multiple aactnum2 for one acctType.
So when I update acctnum1 column in table1 with acctnum2 using where accType1=acctType2, it should be random.
Now what happens is that all acctnum1 having same accType1 is replaced with same value , although multiple values of acctType2 are available in table2 for same acctType.
Is there a way to achieve this?
For eg:
In table1 there are 3 columns:
name1,acctnum1,acctType1
Another table2:
acctType2, acctnum2
Here the acctType column will be join column and there can be multiple aactnum2 for one acctType.
So when I update acctnum1 column in table1 with acctnum2 using where accType1=acctType2, it should be random.
Now what happens is that all acctnum1 having same accType1 is replaced with same value , although multiple values of acctType2 are available in table2 for same acctType.
Is there a way to achieve this?
Tagged:
Answers
Create a substitution rule on acctnum1 for however you want it masked, then in the change managers tab convert it to a sync manager. Then add the acctnum2 column as a target for the manager.
It will create a distinct list of the acctnums in both columns, map it to a new masked randomized value and then mask this back in maintaining the referential integrity.
There's a good example on RG University here: Masking Primary and Foreign Keys - Advanced Operations with Data Masker for SQL Server - Redgate University (red-gate.com)
Just ignore that the video is targeted towards PK/FK relationships - it will still have the outcome you want.
Let me know how you get on!
In my use case I want acctnum1 to have the same values in acctnum2 column(bcos there is a decoding algorithm that will convert the masked value to valid data, so random synchronization will not work). So while preserving the acctnum2 values I need these values to be randomly updated in acctnum1 column.
accttype acctnum
1 xxxxx123xxxx
1 xxxx452xxxx
1 xxxx654xxxx
2 xxxxx487xxxx
table1:
name accttype acctnum
Joe 1 123456
John 1 456248
Jame 2 154785
Jxxx 1 457885
Now I want to synchronize table1 with table2 based on accttype column. Now when I do table to table sync, the output is:
table1:
name accttype acctnum
Joe 1 xxxxx123xxxx
John 1 xxxxx123xxxx
Jame 2 xxxxx487xxxx
Jxxx 1 xxxxx123xxxx
The expected output is:
name accttype acctnum
Joe 1 xxxxx123xxxx
John 1 xxxx452xxxx
Jame 2 xxxxx487xxxx
Jxxx 1 xxxx654xxxx
So, if you see the output I get, all acctnum for accttype 1 are same. But I need that to be randomly updated as shown.