Copy data to another schema

I frequently right click on one or more tables and select Data/Copy data to another schema
from prod to test.

It would be great if Toad could remember the settings in the dialog at least for the session

Would it also be possible to have an option to never ask for empty tables nor show warnings that there were empty tables?

image

Hm, I'm surprised it doesn't remember. I do that for export dataset. I can look at that next week. I'm not really working today. Just looking at messages while I drink some coffee.

You can set this up in Automation Designer and run it from there if you are copying the same tables often. Or if it's different tables but you want the same settings, you could set it up in there but then just change the tables before you run it. I'm not sure if that's more or less work than just changing the settings, but anyway, it's an option.

It is usually the same tables, but it is 23 separate schemas for production and 23 different schemas for test.
I have never used Automation Designer, do you have any links to some documentation on how that works?

There are some videos online - just do a search, you'll find them. After you have a look at those let me know if you still have questions.

Your most recent copy table data actions are already recorded in there.

You can preserve them for re-use like this.

SaveAnActionForReuse

Actions age out of "Action Recall". They'll stay in "App1" (or any other app you create on the left) forever after you paste them there. I think if you run like I did in the video (without the dialog) then you won't get the "table is empty, do you want to continue" prompt.

I just took a look at this -

It actually does remember settings if you are copying a set of tables that you have copied recently (if the prior action is still in Action Recall).

I'm a bit hesitant to load settings from another action otherwise. It may catch some people off guard. But I can add a "save settings as default" button, similar to what we have in Import Table Data.

Another little feature request from me:

It would be very nice in the copy dialog if the "Truncate" option always would be disabled and a warning was displayed if the user enables it.

Some years ago I caused some trouble in our company because of the checked truncate option and a click on the okay button one second too fast... :slight_smile: I am glad that I was not crucified :wink:

Hi Rainer,

As soon as I add a warning, other users will tell me that it's annoying.

Honestly, I think If you are clicking fast like that, you are going to go right past the warning as well. I know I have. The best solution is to just slow down a little, but I hope the new default settings will help too.

-John

You're probably right... Since my near-crucifixion, I pay special attention to that :slight_smile:

Hi John,

It is better now, but it would be great if the destination connection and schema could be remembered too.
I copy over about data from 100-150 tables from production to test now and then, and I do them in chunks, data that is in the same area goes at the same time, then I select the next set of tables and so forth.

Hi Esko,

Dest connection and schema are remembered automatically depending on tables selected, but it ages out, as only so many distinct selections are remembered.

If you want to keep it permanently, use automation designer. Here are some steps you can do:

  1. Do your copy table data as you have been
  2. Go into Automation Designer
  3. In AD, select "Action Recall".
  4. Now, on the right, are your most recent actions. If you sort descending by run date, your most recent will be on top.
  5. select it, then CTRL+C
  6. On the left, near the top, you'll see My Apps -> Default -> App1. Select App1 (or make a new app of your own in the "Default" folder)
  7. Now Paste with CTRL+V in the area on the right.
  8. Your action should be pasted in. You can right-click to give it a more meaningful name.
  9. To run it, you can right-click and choose "Run", or if you want to see the GUI while it runs, then double-click and press "Run".

Hej,

If I copy the same tables the destinations are remembered, If I select new tables I must reselect the destinations.
Would it be possible to remember the destinations in the current session regardless of if I select new tables or not?
Using beta 16.3

I have not had time to test the automation designer yet, but I guess that would primarily still be valid for the table selection, and not the destination selection.
I have twenty-five production schemas being copied into twenty-five test schemas, and it is usually just a subset of the tables, and frequently not the same tables each time.
If I want to do a proper copy from prod to test, I must ask the DBA to take a backup from prod and import it into test, that takes a lot more time in a lot of use cases where the copy data option works great.

Automation designer will remember all details, including target connection and schema.

But I just tested it from Schema Browser.
If I do a Copy Table Data, select another DB and schema, run it.
Then right-click the same table(s) again, the DB and schema is auto-selected.

If the destination connection is not present when I repeat, then it is not auto-connected (but with Automation Designer, it will be)

But I understand what you are saying...it's not always the same tables that you are copying, so maybe this won't be very helpful anyway.

Hi @esko.lakso and @rainer.koenigs

Sorry to revive this dead topic. After we had this conversation, I added a "Save Settings as Default" button to the "Copy Data to Another Schema" window. As it turns out, some users are confused by this feature (because the window also remembers settings of recently copied tables on a per-table- basis).

So I was thinking about:

  1. Keeping the code in place to save settings on a per-table basis
  2. Removing the "save settings as default" button
  3. Adding code so that whenever you open the window and run a data copy, those settings are saved as default. So...
    3a) if you copy table data on a table for which we have saved settings we will always use those
    3b) If you copy table data on a table for which we do NOT have saved settings, we'll use the same settings as whatever you used most recently.

Does that seem reasonable to you? It would give the window a feel of more like "settings remembered automatically" without you having to tell it what to remember. And it seems basically like what you were asking for in the first place.

I have to be honest: I didn't really understand what you mean. My English from school 40 years ago isn't that perfect. My issue was only the thing with the truncate option, but that is already discussed / closed. So it is at Esko to tell his opinion about your suggestions :slight_smile:

Best regrads from Germany
-Rainer

1 Like

Hi John,

That sounds OK to me, consistency in the user experience is important.