Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTEs and Trees Expand / Collapse
Author
Message
Posted Tuesday, December 25, 2007 9:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 5, 2013 8:27 AM
Points: 15, Visits: 118
Comments posted to this topic are about the item CTEs and Trees


Post #436287
Posted Wednesday, December 26, 2007 10:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #436419
Posted Wednesday, December 26, 2007 1:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #436487
Posted Thursday, December 27, 2007 4:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 5, 2013 8:27 AM
Points: 15, Visits: 118
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...



Post #436602
Posted Thursday, June 12, 2008 7:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
what about adding a number column to the master table and using that in your loop?

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #516349
Posted Thursday, June 12, 2008 7:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #516357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse