For the first time I have been forced to use a cursor in something other than administration scripts. I'm just wondering of there is any other way to solve the problem. It will take a bit of explaining.

We have a document storage system where documents are stored under Volume and directory. To further complicate things one document can have multiple revisions. The complicated things even further, we have another level, projects, which stores contains volumes. Each of these levels is contained in a table. A revision corresponds to a file in the filesystem, while a document stores a title and not much else.
Projects
     Volumes
          Directories
               Documents
                    Revisions

This is where it gets interesting.

I just working on something where you create a template for a new project. In the template you can create volumes, and inside the volumes directories. Inside the directories you can add (physical) documents.

However the (physical) documents are sourced from existing projects. To be more accurate the documents are sourced from revisions inside existing projects. The thinking behind this is that the documents can used as breeder documents (ie get revised further) for new projects.

In effect when you add a new project and apply the template to it, all the document volumes, directories and documents will be added. However adding a document involves adding row to the documents table then one to the revisions table. I other words the end result is that there are multiple INSERTS to a parent table and a child table.

I find this hard to do using set based logic. I can't just do:
INSERT INTO xxx
(...)
SELECT

because it involves two tables. If it were just one row I'd be doing an INSERT then using @@IDENTITY or SCOPE_IDENTITY to INSERT INTO the second table. At the moment I am effectively doing just that within a cursor.

I'm just hoping I can keep my record clean by never using a cursor in production code. I could hack it an use a temp table with a loop, but that is basically the same thing.

regards
2 days
David Cameron

__________________
regards
David Cameron

What you are describing looks like a hierarchy to me. It's way too complex a subject to get into here. Get yourself a copy of "SQL for Smarties" by Joe Celko. He devotes a large chapter to the subject, and I understand he's about to publish an entire book on trees and hierarchies in SQL.

You might want to do some research into "nested sets" and/or "adjacency list". A Google search on these terms will point you in the right direction.

Note that these solutions would require a redesign of your tables. If you can't do that, you may find that even though you think the temporary table/loop approach is the same thing as a cursor, it really isn't - the cursor consumes way more resources than the temp table and the enclosing loop...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com