Feature request: Synchronization insert type option

Compares and synchronizes SQL database content.

Moderators: Chris Auckland, David Atkinson, richardjm, david connell

Feature request: Synchronization insert type option

Postby Kasper Bengtsen » Wed Oct 05, 2011 10:48 am

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 :) )
Kasper Bengtsen
 
Posts: 6
Joined: Wed Jul 13, 2011 1:14 pm

Postby james.billings » Wed Oct 05, 2011 5:21 pm

Thanks for your post.

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!
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby Kasper Bengtsen » Thu Oct 06, 2011 8:42 am

I figured it would be hard to explain. :)
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.
Kasper Bengtsen
 
Posts: 6
Joined: Wed Jul 13, 2011 1:14 pm

Postby james.billings » Thu Oct 06, 2011 3:07 pm

No, I think that makes sense.

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!
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby Kasper Bengtsen » Fri Oct 07, 2011 10:16 am

Ok .. again thanks for your quick reponse.

I will check out the SDK (or handle this problem in some other manner).
Kasper Bengtsen
 
Posts: 6
Joined: Wed Jul 13, 2011 1:14 pm


Return to SQL Data Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests