StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMost efficient way to clone database representation of tree in adjacency list model
text
Body
copied!<p>I'm storing nested lists in my database using the adjacency list model. Each list might have 50-150 nodes, so we'll call it 100 nodes on average. The situation has come up where users want to clone a list (i.e., use an existing list as a template from which to create a new list). This use case could potentially save them a lot of time when new lists differ only slightly from existing lists.</p> <p>Here's an abbreviated version of the table schema I'm using:</p> <pre><code>CREATE TABLE Nodes ( NodeId int IDENTITY(1,1) NOT NULL, ParentId int NULL, ListId int NOT NULL, NodeText varchar(255) NOT NULL ) </code></pre> <p>My original thought was to use an <code>INSERT ... SELECT</code> to copy all the nodes in one shot, but that leaves the new records referencing the old <code>ParentId</code> values.</p> <p>I've got a solution that's working (in application code, not SQL), but it seems suboptimal due to the number of queries required. Here's the algorithm:</p> <ol> <li>Select all records belonging to old list.</li> <li>Iterate over rows and add to new list by inserting with different <code>ListId</code>.</li> <li>Select <code>@@IDENTITY</code> from each insert and store it alongside data for current row.</li> <li>Iterate over rows again and update <code>Nodes</code> table, setting <code>ParentId</code> to new ID (from previous step) where <code>ParentId</code> is equal to old ID and <code>ListId</code> is equal to new list ID.</li> </ol> <p>Like I said, that works fine, but it requires 300+ queries to clone a single list containing 100 nodes. Is there a more efficient way to achieve the same thing? </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