Shame on me for using a "special" read-only account for data comparison, but I'm not a fan of the Sync behavior change that I see in 13.1 vs. 12.12. A big fat button to magically change the target vs. a little drop down arrow on the side of the same button to do the only action that I would ever consider doing, send to editor, is just a bad design change in my opinion. 12.12 offered 3 separate but equally sized button options along with more flexibility with the Delete/Insert/Update options. Typically I Insert only the missing stuff and occasionally update, but only after manual reviews and edits.
I switched from 12.12 to 13.1 mid-way in comparing ~400 incoming tables with the consolidated target (think verifying/merging common client reference data when consolidating client databases). Sheer panic hit when I realized what the new Sync button had done. I had done no review of triggers or anything. Boom it was done. No script. Just done. Fortunately there were no triggers or downstream impact and I had another recent copy of the original target data set to repair the deed.
For me a real time sync is almost never used. Having it as the default is a huge risk and now I can only safely perform the work with a real read-only account.
Please reconsider this design change.
Hey Chuck,
- You can click the dropdown arrow next to the "Sync" button to send the script to editor instead.
- If your sync script is just a merge statement (or delete followed by merge) and you'd rather see a script full of individual insert/update/date statements, hold down CTRL while clicking Sync -> Send to Editor.
- To restore the options of doing insert/update/delete individually,
a) Edit Toad.ini (with Toad not running),
b) Find the [DataCompare] section
c) Add this to that section: AdvancedMode=1
After re-reading, I guess I kinda missed the point the first time I read it. (big buttons vs drop-down). #3 above adds insert button above toolbar on "source only", delete on "target only" and update button on "sync". Those buttons will work on selected rows if some rows are selected, or all rows if not.
#3 also adds back the "Synchronization" page, where you can choose exactly what "Sync" is going to do.
John,
Thanks for the rapid feedback and tips. #3 does restore the Synchronization option under the Matches with the previous 3 buttons and options. This gives me the features I need and a method to stay off of the Sync button entirely.
However, I still see the big Sync (default) button sitting right next to the Compare button that I'm required to hit on par with having the Truncate/Drop table in the SB right next to my "favorite-button-that I-click-all-the-time" with no warning or confirmation. It works, but it's a tad bit risky after 10PM.
So...filter right next to drop is a troublemaker? Hmm.... I'm open to suggestions. Maybe I could come up with a way to customize those LHS SB toolbar buttons.
I get your point about the Sync button. Does rollback not alleviate your concerns?
I was freaking too much in the moment even see if rollback was an option, but that would do it too. Being able to select the default of the 3 options on the Sync button and have it persist would probably be the best option.
Related idea since I'm deep in this one - when I find deltas in these 400 table comparisons that I'll have to repeat another dozen times over the next couple of years I have to report the deltas along with what I intend to add/update (SQL files) to some key end users. It would be nice if this included an option to report the contend displayed in the Source only, Target only, and Differences to a single file. I'm currently copying and pasting all of this stuff into a single huge spreadsheet. With the generated SQL, the Insert SQL fully covers the Source only content and the Delete SQL gives the key columns in the delete. The Update SQL only shows the new values, but not the replaced.
This is the dialog that is shown after sync.
regarding your report - how about if the sync script (the one with the individual statements) optionally included differences (commented). Seems like the inserts and deletes need no explanation (if delete, row exists in target but not source. If Insert, row exists in source but not target)
edit: typo. Should have been "need no explanation"
edit 2: ugh, sorry, didn't read it carefully enough again the first time.
I'm not sure what I saw now after the sync. I'll play elsewhere with it.
Deltas commented in the sync script would probably even be easier to manage. In my case the people in the target environment want to know what will be added once then new client is added (Inserts) and the incoming people would see the same answer from the reported Deletes that we would not actually run. For the deltas in the Updates I just need to show them both source and target and let them pick the winner. Different people own different parts of each app so single table level with the sync script is great.
Here's a couple of samples for additional commented content:
Differences (show both just as in grid):
Source FC00 FAST CRUISE XXRMC 2-8-2016 bb
Target FC00 FAST CRUISE JFMM KE 21
Source AM00 AMMO OFF LOAD
Target AM00 AMMO off load
Target only: (still want to report all of the grid content and not just DELETE ... where key = 'AA02')
AA02 additional testing perform required secondary tests
AA03 still more testing
Nothing additional needed for Source Only since that is in the INSERTS.
OK, I'll add an option for commented differences in sync script.
I could also add a "differences report" that would just export the 3 grids to excel. I could include the colors to make it easy to spot the diffs. SQL Statements too if you think that'll be helpful.
Will let you know when it's ready. Probably not next beta but likely before 13.2 GA.