StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can achieve what you need using a temp table in the target database. Since IDs are auto-generated, the following code will not generate any collisions.</p> <p>I'm going to assume the source database is called SourceDb and the target database is called TargetDb. I'm also going to assum this table structure: <br /><strong>Terms</strong>: <em>ID</em>, <em>Text</em> <br /><strong>Relationships</strong>: <em>ParentId</em>, <em>ChildId</em></p> <p>Create a temporary table in TargetDb with this structure: <br /><strong>TempTerms</strong>: <em>OldId</em>, <em>Text</em>, <em>OldParentId</em>, <em>NewId</em>, <em>NewParentId</em></p> <p>The following code will copy your subtree to the target database.</p> <pre><code>declare RootOfSubtreeId SourceDb.Terms.Id%type; TermCursor sys_refcursor; begin --//Copy the data from SourceDb into the TargetDb temp table. --//This query gets the entire subtree of data with the root of the subtree having ID=RootOfSubTreeId. insert into TargetDb.TempTerms ( OldId, Text, OldParentId ) with RelatedTerms as ( select T.ID, T.Text, R.ParentId from SourceDb.Terms T join SourceDb.Relationships R on R.ChildId = T.ID ) select ID, Text, ParentId from RelatedTerms connect by prior ID = ParentId start with ID = RootOfSubtreeId; --//Open a cursor to loop over all of the temporary data. open TermCursor for select * from TargetDb.TempTerms; for term in TermCursor loop --//Insert the item into TargetDb's Terms table and get the new id back. insert into TargetDb.Terms ( ID, Text ) values ( term.Text ) returning ID into NewTermId; --//Update the temp table's NewId column for the newly inserted row. update TargetDb.TempTerms set NewId = NewTermId where OldId = term.OldId; --//Update the temp table's NewParentId column for all children of the newly inserted row. update TargetDb.TempTerms set NewParentId = NewTermId where OldParentId = term.OldId; end loop; --//Add all relationship data to TargetDb using the new IDs found above. insert into TargetDb.Relationships ( ParentId, ChildId ) select NewParentId, NewId from TargetDb.TempTerms where NewParentId is not null; end; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload