Can I turn off deployment of deletes?

BHughes

I don't want to deploy deletes or "Target Only" for any of my tables, but I can only turn it off after a delete has been identified.  Is there a way to turn it off for tables without having to wait for the checkbox to appear?

In the example below, I can only turn it off for the datasources table, but I want to turn it off for all.

Dan_J

Hi @BHughes

Thanks for reaching out to us on this.

Unfortunately there is currently no way to achieve this in the UI, however it is possible to achieve this via the SQL Data Compare Command Line, by utilizing the /exclude:additional switch: https://documentation.red-gate.com/sdc/using-the-command-line/command-line-syntax/switches-used-in-the-command-line

I hope this helps!

BHughes

Hi @Dan_J

Thank you for taking the time to reply.  That switch is meant to exclude objects from comparison, isn't it?  I want them compared, but I want to only deploy inserts and updates.

There is an "Exclude all in target" setting from the UI that was mentioned by support that I will do some testing with.  I'm unsure if it gets saved with the project so that it remains set when the project is run from the CLI.  It would be far more reassuring if the option showed a checkmark or something beside it once it was selected  I also don't see an obvious CLI switch or option for this except for the one you mentioned, but I discounted it because it reads as though it excludes from comparison.

For my project, I've ended up with a pretty wide workaround utilizing CDC so that I can filter out the deletes before they even get to the SQL Data Compare tool. Would have been more convenient if I could simply choose not to deploy deletes.

Dan_J

Hi @BHughes

Thanks for coming back to me on this. My apology for the delay in responding to you!

Unfortunately there isn't a way to exclude objects before the comparison is run. In order to be able to make selections to exclude/include specific objects the comparison must have been run first.

Regarding whether selections made in the UI are saved with the project, my understanding is that they are. Please do let me know if you are finding this to not be the case.

BHughes

Hi @Dan_J

It doesn't appear as though the "Exclude all in target" option in the UI is being saved with the project.  Like I said earlier, without any sort of indication that it's even been selected (looks to be a space for a checkmark beside it, but no checkmark), it's difficult to tell if its even turned on.  When you actually have records for deletion you'll see the checks for individual tables turned off, but if it happens to be an iteration where there are no deletes, there's no way to tell if it's on or off.  I tested in a situation where I did have deletes and saw the individual delete checks disappear when I selected "Exclude all in target".  Saved the project, reopened, recompared, and all of those delete checks appeared again.

In my project, I'm transferring data from one with a low retention period to another where we can preserve the history for a longer period by retaining the last-known state of the records.  I want the inserts and updates, I'm transforming the hard deletes into soft deletes (updates of IsDeleted = 1), and so I want to completely disregard the actual deletes.  It would have been crazy simple if I could just "turn off delete deployment" in SQL Data Compare.

As it stands, I've implemented "instead of delete" triggers on my target tables so that SDC can send the deletes through and I'll just ensure that the IsDeleted flag is set for those records using the trigger instead of running the actual delete on the target.

Thanks for your help with this!

Dan_J

Hi @BHughes

Thanks for your response on this, my sincere apology for the delay in coming back to you!

Just to clarify on the selections being saved, the pre-comparison selections made on the 'Tables and Views' tab are saved as part of the project, however unfortunately SQL Data Compare does not save selections made after the comparison has been run (before deploying). You must deselect the rows of data that exist only in target for each table before running the deployment process.

To clarify also: using the Command Line for comparison,  you can use the /exclude:additional switch when specifying either the /project or /server1, /database1, /server2 and /database2 switches.  The /exclude:additional switch removes the rows of data that exist only in the target from the results and therefore will not be deployed.