CTEs and Trees

  • Frédéric BROUARD

    Old Hand

    Points: 309

    Comments posted to this topic are about the item CTEs and Trees

  • Jeff Moden

    SSC Guru

    Points: 993750

    Great code and explanations, but no test data...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    i want to play with this in detail when i have time, but we had a problem with an app with similar conditions

    we have a table with data and need to return it in hierchial fashion. someone wrote and sp years ago that used a while loop and temp tables. i just rewrote it with a CTE and it seems to run a lot faster

  • Frédéric BROUARD

    Old Hand

    Points: 309

    I make some tests some years ago with different methods :

    -> tree with classic father/son auto reference (in fac my computer file system including the 4 disks I have)

    a) using temp table and a store proc to find some files having '%toto%' in the file name

    b) using a path stored with the data in a column NAVARCHAR(4000)

    c) using interval model

    d) using CTE

    Interval model was the winner...

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    what about adding a number column to the master table and using that in your loop?

  • Jeff Moden

    SSC Guru

    Points: 993750

    Tech_Newbie (6/12/2008)


    help please.

    I have the following situation:

    I want to populate two related tables

    Person (PersonID, PersonName, PersonLName)

    PersonAddress(PersonAddressID, PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County)

    from a table which contains all the data needed

    MasterList(PersonName, PersonLName, PersonAddr, PersonCity, PersonState, Personzip, County)

    The thing is I am not doing this sequentially, I want to select every 7th person starting from the person in the MasterList table at position 3.

    For example:

    I have 100 people in the master list

    Lets say that I want 30 people to be put into the Persons table and into thier associated address tables.

    I want to be able to increment through the MasterList table starting at the person from position 3, and insert every 7th person thereafter into the tables.

    If I get to the end of the table and I have not reached my 30 people, I need to loop back around starting at a new position - 2 and then repeat selecting every 7th person from then on out.

    Important things:

    1. PersonID is an identity, and an auto - incremented value, but within this loop, when I create my entry for Person, I want to turn around and create an entry for PersonAddress, using the newly created PersonID identity value as a foreign key inside of PersonAddress.

    How should I write a cte to be able to acomplish this ? Thanks for any assistance provided

    Ok... third time's the charm... post your request in the correct forum... you'll get better answers quicker.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply