StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><em>I used to do this kind of thing a lot, but my memory is a bit hazy. I'll give you the general idea, hope it can get you pointed in the right direction.</em></p> <p>Basically you can only do this if you've got a reliable second 'unique key' column in the 'parent' table. If not, you'll need to create one.</p> <p>Say we have these tables</p> <pre><code>ITEMS[id, A, key] //id: 'real' id, A: just some column, key: the alternate key HIERARCHY[idparent, idchild] </code></pre> <p>What you want to do is first copy ITEMS from SOURCEDB to TARGETDB, letting TARGETDB create its own values for the id column.</p> <p>Then you need to copy HIERARCHY from SOURCEDB to TARGETDB, but you need to do a join like so to get the new id:</p> <pre><code>SOURCEDB.HIERARCHY.idparent -> SOURCEDB.ITEMS.id -> SOURCEDB.ITEMS.key -> TARGETDB.ITEMS.key -> TARGETDB.ITEMS.id </code></pre> <p>And you need to do the SAME thing for the idchild column.</p> <p>This will give something like this (untested, and rusty, and probably mssql syntax):</p> <pre><code>//step 1 INSERT TARGETDB.ITEMS(A, key) SELECT A, key FROM SOURCEDB.ITEMS //step 2 INSERT TARGETDB.HIERARCHY(idparent, idchild) SELECT T1.id, T2.id FROM SOURCEDB.HIERARCHY AS H1 INNER JOIN SOURCEDB.ITEMS AS I1 ON H1.idparent = I1.id INNER JOIN TARGETDB.ITEMS AS T1 ON I1.key = T1.key INNER JOIN SOURCEDB.ITEMS AS I2 ON H1.idchild = I2.id INNER JOIN TARGETDB.ITEMS AS T2 ON I2.key = T2.key </code></pre> <p>I'm presuming that these two databases are 'connected' enough that you can do cross-database queries. If you have to serialise to file, it gets a bit more ... complicated.</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