StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Quick answer</strong></p> <p>Import into a staging table, but populate mapped ID values <strong><em>from the same sequence</em></strong> used to produce ID values from the destination table. This is guaranteed to avoid conflicts between ID values as DBMS engine supports concurrent access to sequences.</p> <p>With the ID values on the node mapped (see below) re-mapping the ID values for the edges is trivial.</p> <p><strong>Longer answer</strong></p> <p>You will need a mechanism that maps the values between the old keys from the source and new keys in the destination. The way to do this is to create intermediate staging tables that hold the mappings between the old and new kays.</p> <p>In Oracle, autoincrementing keys are usually done with sequences in much the way you've described. You need to construct staging tables with a placeholder for the 'old' key so you can do the re-mapping. Use the same sequence as used by the application to populate the ID values on actual destination database tables. The DBMS allows concurrent accesses to sequences and using the same sequence guarantees that you will not get collisions in the mapped ID values.</p> <p>If you have a schema like:</p> <pre><code>create table STAGE_NODE ( ID int ,STAGED_ID int ) / create table STAGE_EDGE ( FROM_ID int ,TO_ID int ,OLD_FROM_ID int ,OLD_TO_ID int ) / </code></pre> <p>This will allow you to import into the <code>STAGE_NODE</code> table, preserving the imported key values. The insert process puts the original ID from the imported table into STAGED_ID and populates ID from the sequence.</p> <blockquote> <p>Make sure you use the same sequence that's used for populating the ID column in the destination table. This ensures that you won't get key collisions when you go to insert to the final destination table. <em>It is important to re-use the same sequence.</em><br><br>As a useful side effect this will also allow the import to run while other operations are taking place on the table; concurrent reads on a single sequence are fine. If necessary you can run this type of import process without bringing down the applciation.</p> </blockquote> <p>Once you have this mapping in the staging table, ID values in the EDGE table are trivial to compute with a query like: </p> <pre><code>select node1.ID as FROM_ID ,node2.ID as TO_ID from STAGE_EDGE se join STAGE_NODE node1 on node1.STAGED_ID = se.OLD_FROM_ID join STAGE_NODE node2 on node2.STAGED_ID = se.OLD_TO_ID </code></pre> <p>The mapped EDGE values can be populated back into the staging tables using an UPDATE query with a similar join or inserted directly into the destination table from a query similar to the one above.</p>

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