I am trying to copy data from a few tables using "Copy data to another schema"
It fails because it seems it tries to create contraints ( which it is not supposed to, I am interestef just in data ) towards tables not included in the data transfer.
Previously, just a few hours before, I had succesfully copied five tables, but one at a time.
Now, trying to copy agains those tables but it fails and I see that Toad someway remembers something from previous unsuccessful copies.
How can I reset this?
I can see a button to save defaults but nothing to reset them.
And would it be possibile to add an option to tell Toad that it must copy just data and not try to create indexes, constraints and so on?
Regards
Mauro
PS I am using last beta
Hi Mauro,
That window uses the automation designer, so it remembers what options you selected the previous time you copied those tables. Settings are remembered for each set of tables you copy (at least until settings age out).
No matter what is remembered though, you should be able to change the settings to your liking in the dialog. If you want to copy data and not try to create indexes, constraints etc, then set it like this.
With "Create destination tables" unchecked, it won't try to create anything. And if you leave Disable/enable triggers/constraints unchecked too, it won't try to alter those before or after the copy proces.
The "Save settings" as default feature will save the current settings as default, so that if you copy the Copy Table Data window on a set of tables that it doesn't have stored settings for, it will choose the default settings. But you can always override them by changing the options in the dialog.
I don't have a way to reset in there, but there aren't many things to check/uncheck manually. I suppose I could add a rt-click -> Uncheck all though, if that would be helpful.
Also, please let me know if you believe there is some bug here.
-John
I tried but it does not work, previous settings still kick in.
Actually I think that there is some bug behind the scenes.
Ideally a reset button would solve this issue.
Regards
Mauro
PS here are details
-
I changed some settings and saved them as defaults
-
I repeated my copy:
Same as before ( and byt the way I have no idea about how delete the destination connection )
The root of this problem is this: Toad seems to behave differently when copying just one table or more than one.
In the latter case it tries to recreate indexes and contraints.
This should not depend upon the number of tables copied but upon specific settings and there should be the possibility to tell it to create just tables without anything else, no matter how many table are being copied.
And to memeorize settings does not sound great to me, the number of tables may become large in time, each one is a different case and storing settings for all cases would be too difficult to manage.
Perhaps a way to save different named defaults could be useful if someone has recurrent cases, the same way it is possibile to save named statements if there are some particularly important
Regards
Mauro
Same as before
"Save default settings" does not change the settings that are on the screen. It makes the settings that are currently shown, saved for later when you do a "new" copy table data (with tables that you have not previously copied).
If you want to "delete" the destination connection, just pick another connection using the dropdown arrow or '...' button next to it.
The root of this problem is this: Toad seems to behave differently when copying just one table or more than one.
In the latter case it tries to recreate indexes and contraints.
Thanks, I will investigate that and get back to you.
And to memeorize settings does not sound great to me, the number of tables may become large in time, each one is a different case and storing settings for all cases would be too difficult to manage.
There is nothing to manage. By default, only the last 15 are remembered. You can change that in Options -> General. "Save ___ Toad actions per action type"
Perhaps a way to save different named defaults could be useful if someone has recurrent cases, the same way it is possibile to save named statements if there are some particularly important
You can do this in the Automation Designer window. Because this action is automation-enabled, that's why it is remembering settings for the last 15 (configurable) copy actions.
Thank you
I changed my General. "Save ___ Toad actions to 1
I see that it cannot be totally disabled ( 0 is not an allowed value )
However this did not solve t his problem.
The only workaround I could find is to uncheck "Create table if needed" but of course it is very nice to have Toad do the creating job
Have a nice weekend
Mauro
Hi Mauro,
Ok, I just tried this:
- created tables a_copy_test1 and a_copy_test2 (each has a PK and index) in source db.
- Went to schema browser. Right-clicked a_copy_test1 and chose "copy data to another schema"
- I left the "Create destination tables if needed" checked.
- Table and PK and Index was created in target db, and data copied.
- I dropped the table from target DB.
- Repeated the process with A_COPY_TEST1 and A_COPY_TEST2 checked.
- Tables and PKs and Indexes were created.
- dropped the target tables again and tried all one more time
- each time, the table and PK and Index was created
What am I missing? It seems like there was the same behavior each time.
The only way that I could get any error of any kind is if I chose to not delete the table in between tries, and didn't check the "Truncate destination tables" option. Then I got a PK violation, but that was expected.
Looking at the source code, I see no way that Toad will tables here without indexes and PKs.
-John
The problem is exactly that:
indexes and constraints are created for the selected tables but this is wrong, in general, at least it should do that on request ( via a specific option ), there is no way to prevent indexes and contraints creations.
If you copy a table that has a foreign ley towards a table that does not exists in the destination schema when Toad tries to create constraints of course fails so no data copy gets done
Under "create table if needed" there are already two suboptions, I simply would add other two: create index and create contraints.
When needed ( copy a consistent set of tables ) they could be checked, otherwise they could be unchecked and the problem is solved
Regards
Mauro
I see. I have made this change for next beta:
That way you can exclude all constraints and indexes, or only exclude foreign keys if that's what you really need.
-John
1 Like
This should solve this issue, thank you very much
Have a nice day
Mauro
I tried the new beta, it is fine.
Just a very tiny thing: would it be possible not to check "indexes" and "constraints" when checking "create table if needed" ?
Regards
Mauro
HI Mauro,
I made it automatically check them because I wanted to make the old behavior the default. It has been that way a long time and nobody has asked for a change until now.
-John
I understand, no problem, I uncheck them manually every time I need
Regards
Mauro