June 13, 2008 at 8:42 am
help please. I am Using sql server 2005
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(id, PersonName, PersonLName, PersonAddr, PersonCity, PersonState, Personzip, County)
where id is an auto increment identity
However Only cetain persons qualify for selection, out of those certain people, I need to be able to select a subset into a temporary table OR array-like list structure (which I will refer to as a ?temptable from now on) So I am using a set of nested loops and now I am down to the place where I need to process each person within this certain criteria (each person within the ?temptable)
The thing is I am not doing this sequentially, I want to select every 7th person starting from the person in the ?temptable (subset extracted from the MasterList table) at position 3.
For example:
I have 1000 people in the master list, out of that 1000 people, There are only 100 that meet the criteria for selection. Those 100 are the ones that need to go into the ?temptable
Lets say that I want to select 30 people out of that ?temptable to be put into the Persons table and into thier associated PeronAddress table.
I want to be able to increment through the ?temptable starting at the person from position 3, and insert every 7th person thereafter into the Person and PersonAddress tables.
If I get to the end of the ?temptable 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
June 13, 2008 at 9:49 am
Cross-post. Try to avoid that - it dilutes the answers. Most of the regulars read all forums, so it's not going to help (it might actually annoy some folks).
Anyway - returning to the conversation already underway over here:
http://qa.sqlservercentral.com/Forums/Topic516770-338-1.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 13, 2008 at 11:29 am
assumptions: you want to use lname, fname to order your results for selection or order does not matter, fname/lname combo is the primary key in masterlist
DECLARE @iStartPosition INTEGER
SET @iStartPosition=3
WHILE (SELECT COUNT(1) FROM Person) -4
BEGIN
;WITH PersonData (RowID, PersonLName, PersonName) AS
(SELECT Row_Number() OVER (ORDER BY PersonLName, PersonName) AS RowID, PersonLName, PersonName
FROM MasterList)
INSERT INTO Person(PersonName, PersonLName)
SELECT PersonName, PersonLName
FROM PersonData
WHERE (RowID-@iStartPosition) % 7=0 OR RowID=@iStartPosition
SET @iStartPosition=@iStartPosition-1
END
--since you only want 30 people, get rid of any extras that might have been inserted
DELETE FROM Person WHERE PersonID>30
INSERT INTO PersonAddress(PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County)
SELECT PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County
FROM MasterList ML
INNER JOIN Person P ON P.PersonName=ML.PersonName AND P.PersonLName=ML.PersonLName
June 13, 2008 at 11:33 am
didn't see this was underway in a different forum before i posted, sorry about that. also, the code isn't tested so i apologize for any typos and syntax errors i may have made
June 13, 2008 at 12:04 pm
thats ok, thanks for your suggestion, I just clarified by initial post, and it was my mistake of posting in more than one forum, I didn't know that many people actually monitored all the forums so I was just trying to make sure my question was more visible. I guess you can charge that to my inexperience in posting technical programming questions. Thanks to all who are providing assistance in both posts, however. Didn't mean to annoy anyone by double posting
please direct your attention to this ( the same ) discussion here:
http://qa.sqlservercentral.com/Forums/Topic516770-338-1.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply