Options
CHANGE DISTINCT VALUES
mujtabazeshan
Posts: 2 New member
Is there any way I can select DISTINCT values out of a table and then change those values so that all the values in the table get replaced by the new values?????
Any help would be greatly appreciated.
Any help would be greatly appreciated.
Tagged:
Best Answer
-
Options
PlantBasedSQL
Posts: 187 Gold 4
Hi @mujtabazeshan - thank you for your post!
It's absolutely possible to do this because this is how a Synchronisation Manager works. It creates a temporary table which selects all distinct values into it, then maps it to a new masked value and then synchronises this back into the column.
This operation is used most often to replace all PK and all associated FK values but is also used to retain the spread of values in the way you suggest in your original question. I've included some documentation below for further reading but let me know if you have any issues!
Thank you very much.
(Using the sync manager discussed here): https://documentation.red-gate.com/dms6/data-masker-help/version-6-tech-tips/how-do-i-synchronize-changes-to-an-item-with-no-logical-join
(Point 3 covers a similar topic to what you're asking): https://www.red-gate.com/hub/product-learning/data-masker/approaches-masking-e-mail-addresses
Answers
Also the topics that you pointed to are great and worth reading
I gave the Sync Manager a try and its working great - the only problem is that I have a lot of values to be changed - lets say 25 million in a single table is a start - and the DM is taking more than an hour and half to change these values probably cause there is no index created on the staging table that DM created and it does not give me any option to create one either
also I can not change the Substitution Rule in the Sync Manager to a Split Range Manager to speed up the process
Well the idea itself is great to try a similar approach like the Sync Manager manually that gave me the advantage of using Split Range Manager and then updating the target table using join really speeds up the process that took about 20-25 mins to update 25 million records in a single table although I am still checking the validity of the data at the moment but its looks good for a start
Once again thank you so much for your help and I sure will get back to you if I need any more help
Have a lot of great days ahead of you