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