June 13, 2008 at 8:45 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 8:55 am
What order do you plan on using in the selection process from MasterList? As in - positions 3,10,17 based on what ordering scheme?
----------------------------------------------------------------------------------
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 9:13 am
The selections made and inserted into the Persons and PersonsAddress tables will be sorted in sequential order, in other words (after insertion):
For person table:
PersonID Order of Selection from temp list Selection(Not recorded in table) PersonName
1 3 Name
2 10(7th from start) Name2
For PersonAddress table
PersonAddressID PersonID Address
1 (in theory, might actually be another num) 1 Addr1
etc......
June 13, 2008 at 9:25 am
I understand that - but you're still not getting my question. SQL Server doesn't acknowledge physical order, so there's ultimately no way (without something like an autonumbered field) to tell it to select the "third row in physical order". You can get 3rd based on some random order, or 3rd based on a specific column order, but 3rd based on physical order is a non-starter.
If that's what you're angling for (which I suspect you are) - you need to have something like an identity/autonumber on the MASTER LIST, since it will allow you to have something concrete to order on. The identity doesn't guarantee to stay sequential, but it does help you enforce a predictable order.
Let me know if that's where you're heading.
----------------------------------------------------------------------------------
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 10:41 am
The ordering will be done on a temporary table that has been pulled from the master list, that is the temp table /array list structure I was referring to.
June 13, 2008 at 10:49 am
So there will an auto-number available in the master list when you want to tackle this?
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.
If you're not hung up on Physical order, look at perhaps using
ROW_NUMBER() over (order by null)
as your pseudo-ordering, which would give you something to pull the 3,10,17, etc... from. Again though - it's not guaranteed to be consistent, since the physical order is not guaranteed during the SELECT.
----------------------------------------------------------------------------------
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 12:44 pm
Hmm - I see you added some details. Try this on for size - see if this gets you started. No loops or cursors.
drop table #MySubMasterList
drop table #tmpperson
drop table masterlist
drop table person
drop table personaddress
create table MasterList(
PersonName varchar(100),
PersonLName varchar(100),
PersonAddr varchar(100),
PersonCity varchar(100),
PersonState varchar(100),
Personzip varchar(100),
County varchar(100))
create table Person (
PersonID int identity(1,1),
PersonName varchar(100),
PersonLName varchar(100))
create table PersonAddress(
PersonAddressID int identity(1,1),
PersonID int,
PersonAddr varchar(100),
PersonCity varchar(100),
PersonState varchar(100),
PersonZip varchar(100),
County varchar(100))
--this being your "i'm selecting into a sub/temp table:
select identity(int,1,1) as tempID,
*
into #MySubMasterList
from MASTERLIST
--where MyCriteria='true'
create unique clustered index uci_mysub on #MySubMasterList(tempid)
insert #MySubMasterList(personname,personlname,PersonAddr)
select top 100 name,object_id,name from sys.all_columns
--now set up the select:
--select top 30
-- PersonName, PersonLName
--from
-- (
create table #tmpperson(PersonID int , PersonName varchar(100), PersonLName varchar(100))
;with cte1 as
(select ((3-tempID%7)+7)%7 as selgroup,*
from #MySubMasterList
),
cte2 as
(select ROW_NUMBER() over (order by selgroup, tempID) RN,
tempid,
selgroup,
personname,
personlname
from CTE1)
insert into person(PersonName,PersonLName)
output inserted.* into #tmpperson(PersonID, PersonName, PersonLName)
select PersonName,PersonLName
from
(
select top 30
selgroup,
rn,
tempid,
personname,
personlname
from CTE2
order by selgroup,rn,tempid) t
insert PersonAddress( PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County)
select p.PersonID, s.PersonAddr, s.PersonCity, s.PersonState, s.PersonZip, s.County
from
#tmpperson p
inner join
#MySubMasterList s
onp.personname=(s.personname collate database_default) and
p.personlname=(s.personlname collate database_default)
select * from person
select * from personaddress
----------------------------------------------------------------------------------
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 2:13 pm
The test worked
Next week Ill give this a try adapted into my actual implementation and see if it works - Thanks abunch
June 13, 2008 at 9:40 pm
Tech_Newbie (6/13/2008)
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.
[font="Arial Black"]WHY??? [/font]:blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2008 at 10:20 pm
My question exactly, Jeff.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 16, 2008 at 8:10 am
Why? For the purpose of the application I am trying to build:
Those metrics for counting and selection of people are in compliance to regulations on how the process of selection should be performed - in other words, because I have to do it that way
June 16, 2008 at 8:37 am
Tech_Newbie (6/16/2008)
Why? For the purpose of the application I am trying to build:Those metrics for counting and selection of people are in compliance to regulations on how the process of selection should be performed - in other words, because I have to do it that way
Which regulations are those? I only ask because regulatory sampling rules are usually written with a little more flexibility than "every 7th physical record, starting with the 3rd physical record" and if so, there are some much easier (and faster) methods available (like TABLESAMPLE).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 16, 2008 at 8:50 am
Lets say You have 100 seperate lists of a range of 25-50 people each. For the purpose of inputting the data, you compile all people into one list, organized by region. The approach I am using (with temp table storing people by region) processes the compiled list yet treats each region as a seperate list, for which those regulations about the starting and increment numbers apply. Does this answer provide clarification?
June 16, 2008 at 8:57 am
Thanks!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 16, 2008 at 10:34 pm
Tech_Newbie (6/16/2008)
Lets say You have 100 seperate lists of a range of 25-50 people each. For the purpose of inputting the data, you compile all people into one list, organized by region. The approach I am using (with temp table storing people by region) processes the compiled list yet treats each region as a seperate list, for which those regulations about the starting and increment numbers apply. Does this answer provide clarification?
Yes and no... you said you might have 1000 people and 100 might meet a particular criteria. Out of those 100, you want to select 30. It would be just as effective to select every 30/100 rows and a whole lot easier to figure out especially since it could be done in a single pass.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply