SQLServerCentral / Discuss Content Posted by Frederic Brouard / Article Discussions / Article Discussions by Author / CTEs and Trees / Latest Posts

RE: CTEs and Trees

Jeff Moden — Thu, 12 Jun 2008 19:34:22 GMT

[quote][b]Tech_Newbie (6/12/2008)[/b][hr]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 neededMasterList(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 listLets 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[/quote]Ok... third time's the charm... post your request in the correct forum... you'll get better answers quicker.

RE: CTEs and Trees

alen teplitsky — Thu, 12 Jun 2008 19:16:47 GMT

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

RE: CTEs and Trees

Frédéric BROUARD — Thu, 27 Dec 2007 04:11:28 GMT

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 CTEInterval model was the winner...

RE: CTEs and Trees

alen teplitsky — Wed, 26 Dec 2007 13:29:43 GMT

i want to play with this in detail when i have time, but we had a problem with an app with similar conditionswe 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

RE: CTEs and Trees

Jeff Moden — Wed, 26 Dec 2007 10:47:38 GMT

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

CTEs and Trees

Frédéric BROUARD — Tue, 25 Dec 2007 21:45:06 GMT

Comments posted to this topic are about the item [B]CTEs and Trees[/B]