March 29, 2011 at 7:28 am
I have a situation where I'm creating a drawing pool. The person in first place gets 100 entries, 2nd gets 99, etc. I've created a sort of "number table" where I have the ID of the person and the count of the number of entries they need to have in the final table.
My problem is the only way I can think of to insert multiple entries (i.e. 100 for 1st) is to cursor through the above table, get the count and then run a while loop with an insert to insert the appropriate number of rows in the final table. I'm dealing with several thousand participants so this takes a few minutes to run.
I'm wondering if anyone has a thought about how to do this without a cursor/while loop. I can eliminate the cursor and replace that with an outer while loop, but that's just a different flavor of the same thing. I can think of any other way to do, for example 100 inserts into a table without looping.
Can you?
TIA
:unsure:
March 29, 2011 at 7:45 am
;WITH CTE_Names (name, Rank, Tickets) AS (
SELECT TOP 100
name
, ROW_NUMBER() OVER ( ORDER BY Name ) AS Rank
, 101 - ROW_NUMBER() OVER ( ORDER BY Name ) AS Tickets
FROM
[master].sys.objects
ORDER BY
Name
)
--SELECT * FROM CTE_Names
SELECT * FROM CTE_Names Nms INNER JOIN dbo.Tally N ON N.N <= Nms.Tickets
ORDER BY name, Rank, Nms.Tickets, N
March 29, 2011 at 7:48 am
I think I see where you're going. Two things:
1. What is dbo.Tally?
2. Do I just add an Insert above the Select from the CTE to actually perform the insert?
March 29, 2011 at 7:57 am
It's a table with only 1 column. I have all numbers from 1 to 30 000 in there.
March 29, 2011 at 7:58 am
And yes you need to had insert into before the final select.
I consider that step gravy since I don't know your data and not 100% sure that this is what you wanted to do.
Hope you have all you need to finish this project.
March 29, 2011 at 7:59 am
Looks like it might do the trick. I'll give it a shot.
Thanks.
March 29, 2011 at 8:34 am
Here's the final entire thing. Went from minutes to milliseconds. Much thanks.
Declare @counts table (ID int, Entries int)
truncate table dbo.DrawingPool2
-- Get players and compute # entries based on position (1=100, 2=99, etc)
INSERT INTO @counts
SELECT [Score_ID],
case when score_contest_id=9 and Score_Position>100 then 1
when score_contest_id=9 and score_position<=100 then 101-Score_position
when score_contest_id=10 and Score_Position>50 then 1
when score_contest_id=10 and score_position<=50 then 51-Score_position
end as Entries
FROM [NCAAIPD].[dbo].[Scoring]
where score_contest_id in (9,10) and
score_id in (select validatedid from validatedplayers)
-- Insert "n" rows into pool based on count
;WITH CTE_Names (ID, Rank, Entries) AS (
SELECT TOP 100 PERCENT
ID
, ROW_NUMBER() OVER ( ORDER BY ID ) AS Rank
, Entries
FROM
@counts
ORDER BY
ID
)
INSERT INTO dbo.DrawingPool2
SELECT ID
FROM CTE_Names Nms INNER JOIN dbo.Tally N ON N.N <= Nms.Entries
ORDER BY Nms.Entries desc, N
Probably could have done the entire thing in the CTE but this fixes my performance issue and after this year someone else will be responsible. I think this is less complex for them to understand.
March 29, 2011 at 8:38 am
Seems clear enough to me. As longs as it runs in ms now I think you'll be fine. I wouldn't tune this any further unless this query ran a couple 1000 times an hour, or a t least per day.
March 29, 2011 at 8:40 am
Runs once a year
:w00t:
Thanks.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy