Optional delete statements / optional merge instead of inserts
KlausWiesel
Posts: 2 New member
Hello, I have the following scenario here:
Our tables have primary key values that are filled using sequences. Some of the tables we provide have master data in it, that is provided by us, but the customer should be able to enter new masterdata if needed.
So we have special logic in the sequences: On customer systems, they are counting from 100000 and up, on our reference database, they start from 1.
On each release date, I have two databases
a) a reference database with all the metadata neded (with keys starting from 1).
b) an empty database with same structure
Using "RedGate Data Compare" I can create a script for insertion of all relevant data.
The problem when deploying this to our customer database is, that most of the records are already exisiting in the customers database, so i get errors.
The best solution would be one of these:
a) an option that would generate a delete command for each insert
b) an option that would generate a merge instead of the insert
Is this possible / something that may be implemented in the future?
Regards
Our tables have primary key values that are filled using sequences. Some of the tables we provide have master data in it, that is provided by us, but the customer should be able to enter new masterdata if needed.
So we have special logic in the sequences: On customer systems, they are counting from 100000 and up, on our reference database, they start from 1.
On each release date, I have two databases
a) a reference database with all the metadata neded (with keys starting from 1).
b) an empty database with same structure
Using "RedGate Data Compare" I can create a script for insertion of all relevant data.
The problem when deploying this to our customer database is, that most of the records are already exisiting in the customers database, so i get errors.
The best solution would be one of these:
a) an option that would generate a delete command for each insert
b) an option that would generate a merge instead of the insert
Is this possible / something that may be implemented in the future?
Regards
Tagged:
Comments
Thank you for your question! Unfortunately we currently don't support scripting merges or deletes for each insert. However this idea has been raised before on our uservoice channel (https://redgate.uservoice.com/forums/147879-sql-data-compare/suggestions/2795870-add-if-not-exists-to-data-compare) and has gained a lot of votes so we are aware of the interest surrounding this use case. Please feel free to add your vote to any ideas (or post your own) that you think will improve the product.
Thanks,
Asha