CTEs and Trees

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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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...

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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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