Change one column in a table

rchutchrchutch New memberPosts: 5 New member
edited April 23, 2015 1:16PM in SQL Data Generator
Hi,

So, feature request. We have lots and lots of student-related test data that has "grown old". We would like to update a few fields, like change student grade levels or birth dates so that the data will still appear to be reasonable. We don't want to rebuild all of the databases and tables in them, we just need to replace the values in one or two columns in a few of the tables and leave the rest of the columns alone.

Is there an easy way to do this? We have hundreds of inter-related tables, so it is VERY difficult to delete the data in the tables and re-create. We need to leave the spider's nest of linkage information alone while changing the .005% of the demographic information.

If there isn't an easy way of doing this, please consider it a feature request.

Thanks,
Rob
Tagged:

Comments

  • Anu DAnu D Silver 3 Posts: 876 Silver 3
    Hi Rob,

    Thanks for your post!

    I am logging a ticket for you and once we have a solution we can post it here.

    I will email you with my suggestion shortly!
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • rchutchrchutch New member Posts: 5 New member
    As an update, we went back and forth many times and - there is no way to do this.

    The closest thing you can do is to delete, then recreate. This is OK if your data is simple, but does not work if:
    • You have autonumber primary key values that are referenced in other tables. When you re-create your data, if there were any deleted records in the original sample data, the new data will not have those "missing ID values" and you will end up with mismatched records
    • If your records have foreign keys, delete, then recreate will not work - consider cascading deletes, etc.

    I asked for Red-Gate to consider this as a feature request. Who knows?

    I am now looking for another tool to help with this very large and very real problem. Are there others who have the same problem? Perhaps if so, they wouldn't think it is just me with this issue.

    Thanks,
    Rob
  • NoelNoel New member Posts: 2 New member
    edited November 9, 2017 12:00PM
    I have the same issue where I only want to obfuscate specific sensitive columns. Due to the complexity of the industry we are in we need to keep the data as close to real world as possible to enable the most accurate testing and considering our databases being complex this product is useless to us in its current form.
    Due to the changes in reference to GDPR being able to only change specific columns in a table would be highly utilised.
    Looks like I'll have to go back to my original plan and code this up manually into a T-SQL package which will take a considerable amount of time.
    Redgate please consider this extremely useful feature for inclusion in a new version of this tool.
  • Eddie DEddie D Rose Gold 3 Posts: 1,523 Rose Gold 3
    Hi Noel

    Does SQL Data Generator or SQL Data Masker help you in your scenario, where you wish to replace sensitive data with realistic data?

    Further information available here:
    https://www.red-gate.com/products/sql-development/sql-data-generator/

    https://www.red-gate.com/products/dba/data-masker/

    Many Thanks
    Eddie

    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • NoelNoel New member Posts: 2 New member
    Thanks for the reply Eddie

    SQL data Generator does not help as it won't select a single field within a table it only does the whole table (unless I'm missing something), but I don't want to change the whole table just a single column, Data Masker on the other hand looks like it will do what we need. Do you know if Data Masker part of the ToolBelt pack?
  • Eddie DEddie D Rose Gold 3 Posts: 1,523 Rose Gold 3
    Hi Noel

    Thank you for your reply.

    At this time the Data Masker tool is not part of the SQL Toolbelt bundle of products. It has its own separate application, which can be downloaded from this page:
    https://www.red-gate.com/products/dba/data-masker/download

    There is a version for Microsoft SQL Server and for Oracle.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
Sign In or Register to comment.