Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm trying to replicate in SQLfiddle to try to walk down through the tree of dependencies you've illustrated as I think your problem is similar to this <a href="https://stackoverflow.com/questions/10291945/walk-parent-child-tree-with-recursive-cte">SO Answer</a></p> <p>ok - this will walk down your tree for you:</p> <pre><code>create table document ( [documentId] int, [folderId] int ) insert into document values (1222, 1), (1256, 2), (1257, 3) create table folder ( [folderId] int, [parentFolder] int null ) insert into folder values (1, 5), (2, 1), (3, 2), (5, null) </code></pre> <p>Here is the recirsive CTE that walks down the tree and finds the subfolders of subfolders of subfolders ...</p> <pre><code>DECLARE @folderToDelete int = 1 ;WITH RESULT (folders,LEVEL) AS ( --anchor SELECT folderId [folders] ,0 AS LEVEL FROM folder AS E WHERE folderId = @folderToDelete UNION ALL SELECT E.folderId [folders] ,LEVEL +1 --switched parent/child FROM folder AS E INNER JOIN RESULT AS D ON E.parentFolder=D.folders --WHERE LEVEL &lt; 100 ) SELECT * FROM RESULT OPTION (MAXRECURSION 100) </code></pre> <p>I've saved a live example <a href="http://sqlfiddle.com/#!3/69049/3" rel="nofollow noreferrer">here on SQLfiddle</a></p> <p>Once you have this set of folders it should be easy enough to find all the associated documents that you need to move</p> <p>Referring to this <a href="http://blog.sqlauthority.com/2008/07/29/sql-server-sql-server-simple-example-of-recursive-cte-part-2-maxrecursion-prevent-cte-infinite-loop/" rel="nofollow noreferrer">article by Pinal Dave</a> I've amended the way the loop detects it's maximum. Without any sort of catch for the maximum if the loop carried on past 100 iterations the server would error but according to this article it looks like MAXRECURSION can be set as high as 32767....hopefully your file structure isn't that complex! </p>
    singulars
    1. This table or related slice is empty.
    1. POrecursion in sql server in multi level
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USwhytheq
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POrecursion in sql server in multi level
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COgreat answer .. thank you very much , but this line for what (WHERE LEVEL < 100 )
      singulars
      1. PO
      1. USHaneen
    2. COrecursive cte's have a limit on the number of loops they will complete. Can't remember the exact number but I belive it is about 120...at that point it will error; so this line will mean it won't error. Are you ever likely to need it to go beyond 100? I'll edit the answer slightly
      singulars
      1. PO
      1. USwhytheq
    3. COyes i need it to be more than 100 ... can you please make that ..
      singulars
      1. PO
      1. USHaneen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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