Insertion feature

Arun72

Hi All,

I'm trying to insert the data using "Insertion" rule in SQL Data Masker. I was able to insert the data into my new table. Is there any way that I can  insert the data into a table which already has a data in one/some of the columns.
For instance, my table have columns like ID,SSN, Name, Address. I want to insert the data into "Name" column without effecting the existing column data. I have tried this but the rest of the columns are populating as NULL.

Is there any way that I can insert the data only on selected columns.

PS:I have gone through the below link but I am checking is there any way that could satisfy my 
https://documentation.red-gate.com/dms7/data-masker-help/masking-rules/insertion-rules


Thanks,
Arun

PlantBasedSQL

Hi @Arun72

Thank you for your post - Insertion rules are intended to only generate brand new rows of data, so to effectively "Insert" values for the pre existing rows, you will need to use a Substitution Rule for the affected columns, with a WHERE clause set to "No WHERE Clause"

Because you already have some data in the rows, this is technically classed as a masking operation, not insertion.

Let me know if you have any further questions! Thank you very much!

Arun72

Hi @PlantBasedSQL
Thanks for the information. I do have one more question ,Can we upload XML files, JSON files into database records using "Insertion"? I have seen that there is an option to insert from Disk ,and those files resides in the "Data sets ".Since I don't have needed privileges, I can't update the data sets and see whether it supports xml, JSON or not

Thank you,
Arun

PlantBasedSQL

Hi @Arun72 thanks for your comment - I believe this will work with any file so long as you're uploading into the correct datatype column, but obviously you will need access to the datasets folder to specify the file that you would like to be uploaded.
I should also mention that it will be the same file every time that gets uploaded, it won't loop around a selection of files.
This also depends if you'd like to be uploading say full XML or JSON INTO the column, as opposed to the file itself (so I guess depending on if you're using VARBINARY or NVARCHAR(MAX) for your datatype too) as you might be able to (if you don't have access to the datasets directory) write a command rule which updates the table and inserts a particular few lines of XML or JSON into the column - this way you wouldn't need to alter the datasets.

I hope this helps!
Kindest

Arun72

Hi @PlantBasedSQL
Thanks for the reply.
I have got one more question on this. Is there a way that we can insert the data with Referential Integrity. Like in my masking scenarios that you have suggested in my previous posts, If I have two tables with name columns, PK and FKs, based on PK can I insert the same data into two new tables  ?

PlantBasedSQL

Thanks @Arun72 - not a problem at all! Happy to help with any questions (at least the ones I know! )

With the Insertion rule there's no way to currently choose another column on another table as the source of the values. To insert with referential integrity, you'd need to use the FK disable rules in Data Masker to prevent constraint violations, then generate either:

- A set of values consistent with the PK relationship (i.e. a user defined data set or a well defined, well bounded rule) 
OR 
- A set of values you want to be in the FK and then go back through with a row internal rule to change all the values that don't exist in the original PK column

Then then re-enable the FK constraints.

I agree it's not ideal, it's possible, but it's not ideal!

Let me know your thoughts,
Thanks - kindest

Arun72

HI @PlantBasedSQL
Thanks for the explanation .Is there any video course available for the second option that you mentioned ?

Thank you,
Arun

PlantBasedSQL

Hey @Arun72

Thanks for coming back on this - there's no video course per-say however it would be fairly straight forward:

- Disable the PKFK relationships/constraints
- With a substitution rule, generate a bunch of values into the table referenced by the foreign key (for this example let's say integers between 1 and 99)
- With a second insertion rule generate some more values using the same generator e.g. integers from 1 to 99 for the table with the FK reference
- With a row-internal rule, go back through with a case statement on the FK column to do something like CASE WHEN NumberColumn IN (SELECT NumberColumn FROM PKTable) THEN DMSPARAM1 ELSE DMSPARAM2 END with DMSPARAM1 being "Do Nothing" and DMSPARAM2 being Integers between 1 and 99, this will check to see if it exists and then if it's not in the PK it will generate a new value
- Re-enable the PKFK constraints

This isn't fool proof and there is still definitely a chance that a value will be generated that isn't in the PK - the easiest way of doing it would be to use a user-defined data set, but this approach would at least help get you some of the way.

Let me know if you have any questions - thank you very much.
Kindest

Arun72

@PlantBasedSQL
Thank you for the explanation I will try this and let you know.