What are the challenges you face when working across database platforms? Take the survey

Random row selection in table to table synchronization.

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: 
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?


  • Options
    @devi_sree I think you'd be better off forgetting the join and just using a synchronisation manager for this.

    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!
  • Options
    devi_sreedevi_sree Posts: 3 New member
    Thank you @TheMaskedData for the answer.

    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.
  • Options
    @devi_sree I'm not sure I fully understand - are you able to provide an example with some example data perhaps? Like an ideal before and after so I can think about it a bit more and suggest a better example.
  • Options
    devi_sreedevi_sree Posts: 3 New member
    accttype    acctnum
    1                  xxxxx123xxxx
    1                  xxxx452xxxx
    1                  xxxx654xxxx
    2                 xxxxx487xxxx

    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:

    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.
Sign In or Register to comment.