Options
Feature request: Synchronization insert type option
Kasper Bengtsen
Posts: 8
When doing a synchronization script following a compare, it is not possible to take into account that the data being inserted might already exist.
A "synchronization type" (or the like) option for the synchronization script could take this into account, dictating the behaviour of insert statements.
The type options could be:
1. Insert without checking
2. Insert only new rows
(more could be thought up)
This would be checked on the comparison key.
Example of where this would be useful:
If it is possible to do localization of labels at different locations (each with a dedicated database), these localizations could be "harvested" to a central repository, and all localizations be re-deployed back to all installations, using the same synchronization script.
In this case the installation that was the origin of a localized label will have the data already, and checking for existence will be necessary, otherwise the insert will fail.
(.. or am I missing an existing option here
)
A "synchronization type" (or the like) option for the synchronization script could take this into account, dictating the behaviour of insert statements.
The type options could be:
1. Insert without checking
2. Insert only new rows
(more could be thought up)
This would be checked on the comparison key.
Example of where this would be useful:
If it is possible to do localization of labels at different locations (each with a dedicated database), these localizations could be "harvested" to a central repository, and all localizations be re-deployed back to all installations, using the same synchronization script.
In this case the installation that was the origin of a localized label will have the data already, and checking for existence will be necessary, otherwise the insert will fail.
(.. or am I missing an existing option here
) 
Comments
I wasn't quite sure what you meant here unfortunately. If you've done a compare, then the synchronization script will be built from the differences that finds. So it should never end up in a situation where it's trying to insert a record that already exists (it would update it instead).
The only caveat here is if you're talking about where you generate a sync-script from one set of data, but you may want to apply that to another set of data.
This isn't something that you can easily do - even SQL Packager would want to compare against the target database.
It's something you could potentially achieve using the SDK so the comparison phase is actually run against the target database though?
Hope that makes sense - if I've misunderstood, let me know!
Redgate Software
But still, it would be of great value.
I'm talking about a feature similar to the "Action type" option in the scripting tool http://www.sqlscripter.com/
We currently use this tool along with the RedGate compare tools, exactly because the RedGate tools do not support this.
(I probably just violated some forum rules right there ... just edit post to comply)
It has value when you apply the same synchronization script to several databases, and some databases might already contain the data being inserted.
That way the script can be used as part of an upgrade package.
All our products currently build their scripts from an A>B comparison so for upgrades where data may already exist, there's no easy way around it right now.
The SDK solution I mentioned (if you want to go down the road of writing your own app using our DLLs) is the only way as then the comparison is occurring directly against the target rather than "in advance".
Hope that makes sense!
Redgate Software
I will check out the SDK (or handle this problem in some other manner).