Change one column in a table

rchutch · April 9, 2015 4:59PM

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

Anu D · April 13, 2015 4:29AM

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!

rchutch · April 23, 2015 1:16PM

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

Noel · November 9, 2017 11:59AM

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 D · November 10, 2017 10:29AM

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

Noel · November 10, 2017 10:47AM

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 D · November 10, 2017 1:48PM

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

Change one column in a table

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk