Hi All
This has to be something I am missing…
I need to pull a subset of data from a table in an existing database to another on a different instance.
The problem I have is that the new table has a UID field which is populated by a sequence / trigger pair and the data subset has no UID field.
I played around for an hour or more before I resorted to coding the PL-SQL to do the job - this is surely one of the things which TOAD is designed to help with!
Thanks for any advice - I will gladly admit to being a contender for the Technical With All Things award for this week when somebody points me to the simple solution…
Steve
Create a pre-insert trigger to populate the one column, and then use data subset
wizard (or export/data pump wizards). Just make the column optional with a
default – for the moment. Then load the data. Then make column mandatory
(if it needs to be) and drop the default and trigger.
If you have Toad 10 –
If you need to create a sequence-trigger pair on a table, go the schema browser.
Right-click on the table, and choose Create -> Trigger/Sequence pair.
If you want to pull a percentage of the rows from the source to destination
table, You can use the Data subset wizard (Under Database -> Export). That
will create a script to move data from every table in the schema, so you if you
are just interested in one or a few tables, you’ll need to edit the
script.
If you can get the rows you want to move with a where clause (or leave it off to
copy all rows), try right-clicking the source table in the schema browser and
choosing “copy data to another schema”.
I believe both of these methods will require the same columns to be in each
table. If they are not, one thing you could do is add the missing columns to the
destination table, copy the data over, and then drop the columns you added.
-John