InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:14:53 GMT20http://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]mister.magoo (3/12/2013)[/b][hr]Drop #TheChosenOnes on it's own first and then run the whole thing.[/quote]Ok, coworker managed to get it to work now it works for me. He didn't do any changes to it. I pasted the code into an email, he copied it out of the email and managed to alter the stored procedure with the code. I'm not going to try and figure this out any more and I'm just going with what I got! Thanks again, everyone, for your time and expertise. You've all been massively patient with me and I appreciate that as much as I appreciate you working this out for me.Tue, 12 Mar 2013 11:30:29 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxDrop #TheChosenOnes on it's own first and then run the whole thing.Tue, 12 Mar 2013 10:48:38 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]mister.magoo (3/12/2013)[/b][hr][quote][b]Siobhan Perricone (3/12/2013)[/b][hr]As far as I know, they're supposed to be. I mean, we told the server folks who set things up that they needed to be exactly the same.However, when I open up prod in management studio it says SQL Server 9.0.5069 and dev says SQL Server 9.0.4266[/quote]And the compatibility level on the databases themselves?[/quote]Both say they are SQL Server 2005 (90)Tue, 12 Mar 2013 10:45:29 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]Siobhan Perricone (3/12/2013)[/b][hr]As far as I know, they're supposed to be. I mean, we told the server folks who set things up that they needed to be exactly the same.However, when I open up prod in management studio it says SQL Server 9.0.5069 and dev says SQL Server 9.0.4266[/quote]And the compatibility level on the databases themselves?Tue, 12 Mar 2013 10:38:14 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxAs far as I know, they're supposed to be. I mean, we told the server folks who set things up that they needed to be exactly the same.However, when I open up prod in management studio it says SQL Server 9.0.5069 and dev says SQL Server 9.0.4266Tue, 12 Mar 2013 10:36:44 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]Siobhan Perricone (3/12/2013)[/b][hr]Curiouser and curiouser, it runs on my production system, but no on my dev system. But I just imported the data from prod to dev a short bit ago.[/quote]are both set at the same compatibility level? on the same version?Tue, 12 Mar 2013 10:32:19 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxWould it be a dumb question to ask what version of SQL Server you are running?Tue, 12 Mar 2013 10:32:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxCuriouser and curiouser, it runs on my production system, but no on my dev system. But I just imported the data from prod to dev a short bit ago.Tue, 12 Mar 2013 10:30:20 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]ChrisM@Work (3/12/2013)[/b][hr]This solution I belive matches your description:[/quote]I tried working with this code and it parses fine but when I try running it, it throws this series of errors:Msg 207, Level 16, State 1, Procedure anrsp_AwardFirewoodLotsSecondMethod, Line 45Invalid column name 'rn'.Msg 207, Level 16, State 1, Procedure anrsp_AwardFirewoodLotsSecondMethod, Line 62Invalid column name 'rn'.Msg 207, Level 16, State 1, Procedure anrsp_AwardFirewoodLotsSecondMethod, Line 84Invalid column name 'rn'.I thought maybe I'd mucked something up, so I tried copying and pasting it again, and it's still munging on me. I can see where rn is being set, and I can't figure out why the other bits of code can't see it. [quote][b]ChrisM@Work (3/12/2013)[/b][code="sql"]IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;IF OBJECT_ID('tempdb..#RemainingPunters') IS NOT NULL DROP TABLE #RemainingPunters;IF OBJECT_ID('tempdb..#TheChosenOnes') IS NOT NULL DROP TABLE #TheChosenOnes -- Fill a small pot from a big table of hopefulsSELECT TOP(80) -- reduced size of pot to fit sample data!!! rn = ROW_NUMBER() OVER (ORDER BY NEWID()), FirewoodLottery_id, Choice1, Choice2 INTO #TheChosenOnes FROM [survey].[FirewoodLottery]ORDER BY NEWID() -- Allocate as many first choices as possibleSELECT Choice = 1, t.FirewoodTowns_id, t.town, t.Permits, PermitsLeft = t.Permits - COUNT(*) OVER(PARTITION BY t.town), x.FirewoodLottery_id INTO #FirstChoiceFROM survey.FirewoodTowns tCROSS APPLY ( -- randomly pick folks for available pitches SELECT TOP(t.Permits) l.FirewoodLottery_id FROM #TheChosenOnes l WHERE l.Choice1 = t.town -- first choice ORDER BY rn) xORDER BY t.town;-- What pitches remain after first allocation? SELECT DISTINCT t.FirewoodTowns_id, t.town, t.Permits, PermitsLeft = ISNULL(PermitsLeft,t.Permits)INTO #RemainingPitches FROM survey.FirewoodTowns tLEFT JOIN #FirstChoice f ON f.FirewoodTowns_id = t.FirewoodTowns_idWHERE ISNULL(PermitsLeft,t.Permits) > 0-- What remains of the chosen ones after the first allocation?SELECT l.rn, l.FirewoodLottery_id, l.Choice2INTO #RemainingPuntersFROM #TheChosenOnes lWHERE NOT EXISTS (SELECT 1 FROM #FirstChoice f WHERE f.FirewoodLottery_id = l.FirewoodLottery_id)-- Allocate any remaining pitches as second choicesSELECT Choice = 2, t.FirewoodTowns_id, t.town, Permits = t.Permits, PermitsLeft = t.PermitsLeft - COUNT(*) OVER(PARTITION BY t.town), x.FirewoodLottery_id INTO #SecondChoiceFROM #RemainingPitches tCROSS APPLY ( -- randomly pick from remaining punters SELECT TOP(t.PermitsLeft) l.FirewoodLottery_id FROM #RemainingPunters l WHERE l.Choice2 = t.town -- second choice ORDER BY rn) xORDER BY t.town; -- ResultsSELECT * FROM #FirstChoiceUNION ALLSELECT * FROM #SecondChoiceORDER BY FirewoodTowns_ID, Choice, FirewoodLottery_id; [/code][/quote]Tue, 12 Mar 2013 10:27:54 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]TheSQLGuru (3/12/2013)[/b][hr][quote][b]ChrisM@Work (3/12/2013)[/b][hr][quote][b]TheSQLGuru (3/12/2013)[/b][hr]One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.[/quote]Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it". It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.[/quote]Getting a bit off-topic here, but:A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process. B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit. :hehe:[/quote]We'll have to agree to disagree for now because I can't fault the sense of your words. I time virtually everything I write and have done for quite a while. You can tell if I've written a query because it's likely to have something like this after it;[code="sql"]-- (24611 row(s) affected) / 00:00:01[/code];-) I'd love to have enough time to set up a decent test to demonstrate what I'm seeing, but it won't happen this week.Tue, 12 Mar 2013 09:02:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]ChrisM@Work (3/12/2013)[/b][hr][quote][b]TheSQLGuru (3/12/2013)[/b][hr]One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.[/quote]Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it". It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.[/quote]Getting a bit off-topic here, but:A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process. B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit. :hehe:Tue, 12 Mar 2013 08:49:08 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]Siobhan Perricone (3/12/2013)[/b][hr]Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. :) I'm still poking at it and trying to be sure I understand it before I try tweaking.[/quote]Because of the random selection at the start of this process, you get different results every time the code is run. That's a real PITA because other folks can't see the same data as you and verify your assumptions. To get around it, here's a fixed set of 80 rows of "chosen ones" for folks to play with:[code="sql"]SELECT rn, FirewoodLottery_id, Choice1, Choice2 INTO #TheChosenOnes FROM ( SELECT rn = 1, FirewoodLottery_id = 100, Choice1 = 'Shrewsbury', Choice2 = 'Shrewsbury' UNION ALL SELECT 2, 6, 'Orange', 'Groton' UNION ALL SELECT 3, 83, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 4, 12, 'Orange', 'Groton' UNION ALL SELECT 5, 104, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 6, 4, 'North Duxbury', 'North Duxbury' UNION ALL SELECT 7, 49, 'Groton', 'Groton' UNION ALL SELECT 8, 27, 'Groton', 'Orange' UNION ALL SELECT 9, 64, 'Johnson', 'Johnson' UNION ALL SELECT 10, 29, 'Orange', 'Groton' UNION ALL SELECT 11, 98, 'Plymouth', 'Shrewsbury' UNION ALL SELECT 12, 117, 'Groton', 'Orange' UNION ALL SELECT 13, 109, 'Groton', 'Orange' UNION ALL SELECT 14, 86, 'North Duxbury', 'Shrewsbury' UNION ALL SELECT 15, 45, 'Orange', 'Groton' UNION ALL SELECT 16, 94, 'Westmore', 'Westmore' UNION ALL SELECT 17, 62, 'Orange', 'Groton' UNION ALL SELECT 18, 32, 'Groton', 'Orange' UNION ALL SELECT 19, 56, 'Westmore', 'Westmore' UNION ALL SELECT 20, 26, 'Westmore', 'Groton' UNION ALL SELECT 21, 33, 'Groton', 'Orange' UNION ALL SELECT 22, 120, 'Westmore', 'North Duxbury' UNION ALL SELECT 23, 9, 'Westmore', 'Westmore' UNION ALL SELECT 24, 2, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 25, 84, 'Johnson', 'North Duxbury' UNION ALL SELECT 26, 65, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 27, 19, 'Westmore', 'Westmore' UNION ALL SELECT 28, 89, 'Plymouth', 'Shrewsbury' UNION ALL SELECT 29, 76, 'Johnson', 'Groton' UNION ALL SELECT 30, 96, 'Groton', 'Orange' UNION ALL SELECT 31, 59, 'Shrewsbury', 'Shrewsbury' UNION ALL SELECT 32, 38, 'Orange', 'Groton' UNION ALL SELECT 33, 17, 'Groton', 'Orange' UNION ALL SELECT 34, 79, 'Johnson', 'North Duxbury' UNION ALL SELECT 35, 116, 'Orange', 'Orange' UNION ALL SELECT 36, 30, 'Westmore', 'Groton' UNION ALL SELECT 37, 77, 'Groton', 'Johnson' UNION ALL SELECT 38, 14, 'Shrewsbury', 'Shrewsbury' UNION ALL SELECT 39, 20, 'Westmore', 'Westmore' UNION ALL SELECT 40, 106, 'North Duxbury', 'Groton' UNION ALL SELECT 41, 103, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 42, 105, 'Orange', 'Groton' UNION ALL SELECT 43, 31, 'Groton', 'Orange' UNION ALL SELECT 44, 92, 'Johnson', 'Johnson' UNION ALL SELECT 45, 69, 'Westmore', 'Westmore' UNION ALL SELECT 46, 118, 'Orange', 'Westmore' UNION ALL SELECT 47, 51, 'Westmore', 'Westmore' UNION ALL SELECT 48, 18, 'Westmore', 'Westmore' UNION ALL SELECT 49, 7, 'Orange', 'Groton' UNION ALL SELECT 50, 54, 'Westmore', 'Westmore' UNION ALL SELECT 51, 75, 'Orange', 'Groton' UNION ALL SELECT 52, 88, 'Orange', 'Groton' UNION ALL SELECT 53, 99, 'Plymouth', 'Shrewsbury' UNION ALL SELECT 54, 78, 'Groton', 'Orange' UNION ALL SELECT 55, 113, 'Johnson', 'Johnson' UNION ALL SELECT 56, 1, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 57, 95, 'North Duxbury', 'Westmore' UNION ALL SELECT 58, 46, 'Groton', 'Groton' UNION ALL SELECT 59, 73, 'North Duxbury', 'Johnson' UNION ALL SELECT 60, 72, 'Westmore', 'Westmore' UNION ALL SELECT 61, 110, 'Orange', 'Groton' UNION ALL SELECT 62, 22, 'Johnson', 'North Duxbury' UNION ALL SELECT 63, 97, 'North Duxbury', 'Roxbury' UNION ALL SELECT 64, 115, 'Roxbury', 'Orange' UNION ALL SELECT 65, 16, 'Groton', 'Orange' UNION ALL SELECT 66, 11, 'North Duxbury', 'North Duxbury' UNION ALL SELECT 67, 114, 'Westmore', 'Westmore' UNION ALL SELECT 68, 58, 'Shrewsbury', 'Shrewsbury' UNION ALL SELECT 69, 34, 'Westmore', 'Groton' UNION ALL SELECT 70, 63, 'Johnson', 'Plymouth' UNION ALL SELECT 71, 47, 'Roxbury', 'Orange' UNION ALL SELECT 72, 74, 'Westmore', 'Westmore' UNION ALL SELECT 73, 111, 'Orange', 'Orange' UNION ALL SELECT 74, 43, 'Groton', 'Orange' UNION ALL SELECT 75, 5, 'North Duxbury', 'Johnson' UNION ALL SELECT 76, 91, 'Shrewsbury', 'Plymouth' UNION ALL SELECT 77, 101, 'Shrewsbury', 'Shrewsbury' UNION ALL SELECT 78, 102, 'Plymouth', 'Shrewsbury' UNION ALL SELECT 79, 80, 'Westmore', 'Groton' UNION ALL SELECT 80, 107, 'Westmore', 'Westmore' ) d[/code]Tue, 12 Mar 2013 08:36:11 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]TheSQLGuru (3/12/2013)[/b][hr]One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.[/quote]Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it". It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.Tue, 12 Mar 2013 08:31:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxOne minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.Tue, 12 Mar 2013 08:06:34 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]Siobhan Perricone (3/12/2013)[/b][hr][quote][b]ChrisM@Work (3/12/2013)[/b][hr]Gosh :blush: thanks!A gentle reminder - [i]always always [/i]test :-) If this solution works, it can be condensed and tweaked for performance.[/quote]Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. :) I'm still poking at it and trying to be sure I understand it before I try tweaking.[/quote]Proper job! A number of folks around here have a line in their sig saying something like "don't use any code from the internet without first fully understanding how it works". If in doubt, just ask. It's quite straightforward stuff.Tue, 12 Mar 2013 07:57:02 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]ChrisM@Work (3/12/2013)[/b][hr]Gosh :blush: thanks!A gentle reminder - [i]always always [/i]test :-) If this solution works, it can be condensed and tweaked for performance.[/quote]Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. :) I'm still poking at it and trying to be sure I understand it before I try tweaking.Tue, 12 Mar 2013 07:50:15 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxNo worries mate. I had the day off yesterday and picked it up at home - when you hadn't posted up by this morning it was ready to go.Tue, 12 Mar 2013 07:28:11 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxThanks for picking this up and seeing it through Chris. I got slammed yesterday afternoon and couldn't get back to this one.Tue, 12 Mar 2013 07:25:30 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxGosh :blush: thanks!A gentle reminder - [i]always always [/i]test :-) If this solution works, it can be condensed and tweaked for performance.Tue, 12 Mar 2013 07:13:29 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxI am completely flabbergasted. You just rock SO HARD! Thank you so much!Actually, thank all of you so much. This has not only been helpful but I'm actually learning!I feel like those words are insufficient but if I continue I'll become insanely effusive. So just, thanks.Tue, 12 Mar 2013 06:57:55 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxThis solution I belive matches your description:[code="sql"]IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;IF OBJECT_ID('tempdb..#RemainingPunters') IS NOT NULL DROP TABLE #RemainingPunters;IF OBJECT_ID('tempdb..#TheChosenOnes') IS NOT NULL DROP TABLE #TheChosenOnes -- Fill a small pot from a big table of hopefulsSELECT TOP(80) -- reduced size of pot to fit sample data!!! rn = ROW_NUMBER() OVER (ORDER BY NEWID()), FirewoodLottery_id, Choice1, Choice2 INTO #TheChosenOnes FROM [survey].[FirewoodLottery]ORDER BY NEWID() -- Allocate as many first choices as possibleSELECT Choice = 1, t.FirewoodTowns_id, t.town, t.Permits, PermitsLeft = t.Permits - COUNT(*) OVER(PARTITION BY t.town), x.FirewoodLottery_id INTO #FirstChoiceFROM survey.FirewoodTowns tCROSS APPLY ( -- randomly pick folks for available pitches SELECT TOP(t.Permits) l.FirewoodLottery_id FROM #TheChosenOnes l WHERE l.Choice1 = t.town -- first choice ORDER BY rn) xORDER BY t.town;-- What pitches remain after first allocation? SELECT DISTINCT t.FirewoodTowns_id, t.town, t.Permits, PermitsLeft = ISNULL(PermitsLeft,t.Permits)INTO #RemainingPitches FROM survey.FirewoodTowns tLEFT JOIN #FirstChoice f ON f.FirewoodTowns_id = t.FirewoodTowns_idWHERE ISNULL(PermitsLeft,t.Permits) > 0-- What remains of the chosen ones after the first allocation?SELECT l.rn, l.FirewoodLottery_id, l.Choice2INTO #RemainingPuntersFROM #TheChosenOnes lWHERE NOT EXISTS (SELECT 1 FROM #FirstChoice f WHERE f.FirewoodLottery_id = l.FirewoodLottery_id)-- Allocate any remaining pitches as second choicesSELECT Choice = 2, t.FirewoodTowns_id, t.town, Permits = t.Permits, PermitsLeft = t.PermitsLeft - COUNT(*) OVER(PARTITION BY t.town), x.FirewoodLottery_id INTO #SecondChoiceFROM #RemainingPitches tCROSS APPLY ( -- randomly pick from remaining punters SELECT TOP(t.PermitsLeft) l.FirewoodLottery_id FROM #RemainingPunters l WHERE l.Choice2 = t.town -- second choice ORDER BY rn) xORDER BY t.town; -- ResultsSELECT * FROM #FirstChoiceUNION ALLSELECT * FROM #SecondChoiceORDER BY FirewoodTowns_ID, Choice, FirewoodLottery_id; [/code]Tue, 12 Mar 2013 06:53:09 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxJust wanted to add that I think this is likely to work out fine. I just need approval from my boss for this method.Tue, 12 Mar 2013 06:42:50 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxThis proposed solution is slightly different. Start by allocating punters picked at random, to pitches, based on punter's first choice. Using the sample set, most towns have all pitches allocated, one town has a few pitches left. All first choices which can be allocated have been, so we move on to second choices. To help with this we prepare two new lists - remaining pitches for each town, and remaining punters. Then the same type of query is run again using second choice.I think this could be very easily modified so that the punters are allocated sequentially until pitches are exhausted instead of at random. Give me 5 mins.Tue, 12 Mar 2013 06:34:36 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxHoly cow! That's brilliant and thank you. What I'm wondering, though, is if will bring the expected result, or if it matters. I'm not great at stepping through the logic on this, so I do need to make sure I understand it.I need to be able to defend the process here to people who used to do this manually with paper (which is something I did myself last week just to get them the list already) as well as being able to adequately defend it to all the applicants, some of whom may inevitably complain. The sequence of award is important for how we've done it in the past, but I'm not sure that it's important now. The way it was done in the past (and the way I was trying to do it this time) was the table got randomized. We started with the first record and subtracted one from the number of lots available for their first choice, then to the next record. This usually goes on for several records, each of the first ones getting their first choices. Then the first time we get to someone whose first choice is no longer available, that person gets their second choice awarded. And so you go down the list, if the first choice isn't available, and the second choice isn't available, then that person doesn't get anything and you move onto the next name. This goes on until all of the lots have been awarded and there are none left. Then you do the whole thing again for assigning the alternates (which are a different set of numbers).So I'm just trying to 1) make sure I understand what the code you've written here does (so I can explain it, as I said), and 2) make sure we've got a legal leg to stand on if this way is different than what we've been doing. Also, I just don't always think about these things in the right ways. I've come to coding through several intermediary steps that haven't included much formal training. Most of what I know is self-taught through trial and error and largely in something of an emergency mode.Tue, 12 Mar 2013 06:15:59 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxHere's a proposed solution. I've broken it up into stages using #temp tables which can be read for the purpose of verifying the results.[code="sql"]IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;IF OBJECT_ID('tempdb..#RemainingPunters') IS NOT NULL DROP TABLE #RemainingPunters;IF OBJECT_ID('tempdb..#TheChosenOnes') IS NOT NULL DROP TABLE #TheChosenOnes -- Fill a small pot from a big table of hopefulsSELECT TOP(800) FirewoodLottery_id, Choice1, Choice2 INTO #TheChosenOnes FROM [survey].[FirewoodLottery] ORDER BY NEWID()-- Allocate as many first choices as possibleSELECT Choice = 1, t.FirewoodTowns_id, t.town, t.Permits, PermitsLeft = t.Permits - COUNT(*) OVER(PARTITION BY t.town), x.FirewoodLottery_id INTO #FirstChoiceFROM survey.FirewoodTowns tCROSS APPLY ( -- randomly pick folks for available pitches SELECT TOP(t.Permits) l.FirewoodLottery_id FROM #TheChosenOnes l WHERE l.Choice1 = t.town -- first choice ORDER BY NEWID()) xORDER BY t.town;-- What pitches remain after first allocation? SELECT DISTINCT t.FirewoodTowns_id, t.town, t.Permits, PermitsLeft = ISNULL(PermitsLeft,t.Permits)INTO #RemainingPitches FROM survey.FirewoodTowns tLEFT JOIN #FirstChoice f ON f.FirewoodTowns_id = t.FirewoodTowns_idWHERE ISNULL(PermitsLeft,t.Permits) > 0-- What remains of the chosen ones after the first allocation?SELECT l.FirewoodLottery_id, l.Choice2INTO #RemainingPuntersFROM #TheChosenOnes lWHERE NOT EXISTS (SELECT 1 FROM #FirstChoice f WHERE f.FirewoodLottery_id = l.FirewoodLottery_id)-- Allocate any remaining pitches as second choicesSELECT Choice = 2, t.FirewoodTowns_id, t.town, Permits = t.Permits, PermitsLeft = t.PermitsLeft - COUNT(*) OVER(PARTITION BY t.town), x.FirewoodLottery_id INTO #SecondChoiceFROM #RemainingPitches tCROSS APPLY ( -- randomly pick from remaining punters SELECT TOP(t.PermitsLeft) l.FirewoodLottery_id FROM #RemainingPunters l WHERE l.Choice2 = t.town -- second choice ORDER BY NEWID()) xORDER BY t.town; -- ResultsSELECT * FROM #FirstChoiceUNION ALLSELECT * FROM #SecondChoiceORDER BY FirewoodTowns_ID, Choice, FirewoodLottery_id; [/code]Tue, 12 Mar 2013 03:48:31 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxHere's a working dataload:[code="sql"]-- set identity insert on for lotterySET IDENTITY_INSERT survey.FirewoodLottery ON -- entering sample data into lottery table INSERT INTO [survey].[FirewoodLottery](FirewoodLottery_id --DateValue, Value, YearValue, Monthvalue,,[Firstname],[MiddleInitial],[Lastname],[Suffix],[address],[city],[state],[zip],[phone],[email],[postdate],[ip],[choice1],[choice2],[assigned],[status],[District],[sortindex],[choice],[Notes]--,[possible_dup]) select 1,'Wombat',NULL,'Champine',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth', NULL,'Dupe',NULL,NULL,NULL,NULL UNION all select 2,'Wombat',NULL,'Beaudry',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth', NULL,NULL,NULL,NULL,NULL,NULL UNION all select 3,'Wombat',NULL,'Couture',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton', NULL,NULL,NULL,NULL,NULL,NULL UNION all select 4,'Wombat',NULL,'VanHorn',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 5,'Wombat',NULL,'Roach',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 6,'Wombat',NULL,'Relation',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 7,'Wombat',NULL,'LaRose',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 8,'Wombat',NULL,'Deuso',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 9,'Wombat',NULL,'Valley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 10,'Wombat',NULL,'Beaudoin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 11,'Wombat',NULL,'VanHorn',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 12,'Wombat',NULL,'Robinson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 13,'Wombat',NULL,'Green',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 14,'Wombat',NULL,'Wheeler',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 15,'Wombat',NULL,'Trombley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 16,'Wombat',NULL,'Trombley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 17,'Wombat',NULL,'Compo',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 18,'Wombat',NULL,'Kipp',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 19,'Wombat',NULL,'Auger',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 20,'Wombat',NULL,'Auger',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 21,'Wombat',NULL,'Elliott','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 22,'Wombat',NULL,'Morse',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 23,'Wombat',NULL,'Morse',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 24,'Wombat',NULL,'Beauchemin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 25,'Wombat',NULL,'Curtis',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 26,'Wombat',NULL,'charron','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 27,'Wombat',NULL,'Barnett','IV','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 28,'Wombat',NULL,'Duke',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 29,'Wombat',NULL,'Gerrish',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 30,'Wombat',NULL,'charron',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 31,'Wombat',NULL,'Smith',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 32,'Wombat',NULL,'Gonyaw','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 33,'Wombat',NULL,'Gonyaw','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 34,'Wombat',NULL,'charron',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 35,'Wombat',NULL,'charron',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 36,'Wombat',NULL,'West',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 37,'Wombat',NULL,'Gerrish','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 38,'Wombat',NULL,'Clark','G','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 39,'Wombat',NULL,'Palmer','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 40,'Wombat',NULL,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 41,'Wombat',NULL,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 42,'Wombat',NULL,'Moser',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 43,'Wombat',NULL,'beyor',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all select 44,'Wombat',NULL,'richart','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Roxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 45,'Wombat',NULL,'Tighe',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 46,'Wombat',NULL,'Abare',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 47,'Wombat',NULL,'Willette',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 48,'Wombat',NULL,'Hinman',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 49,'Wombat',NULL,'Barnett','111','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 50,'Wombat',NULL,'Ware',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 51,'Wombat',NULL,'Ware',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 52,'Wombat',NULL,'Hackett',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 53,'Wombat',NULL,'Drinkwater',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 54,'Wombat',NULL,'Hale',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 55,'Wombat',NULL,'John',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 56,'Wombat',NULL,'John',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 57,'Wombat',NULL,'John',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 58,'Wombat',NULL,'alexander',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 59,'Wombat',NULL,'rogers',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 60,'Wombat',NULL,'Webster','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 61,'Wombat',NULL,'Celley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 62,'Wombat',NULL,'Gerrish','Sr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 63,'Wombat',NULL,'Wilson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 64,'Wombat',NULL,'Tuthill',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 65,'Wombat',NULL,'hall',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 66,'Wombat',NULL,'Habel',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Roxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 67,'Wombat',NULL,'Barnett','Jean','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 68,'Wombat',NULL,'Hudson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 69,'Wombat',NULL,'Carleton','L','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 70,'Wombat',NULL,'Paige',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 71,'Wombat',NULL,'Brown',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 72,'Wombat',NULL,'Rhodes',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 73,'Wombat',NULL,'Wyckoff',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 74,'Wombat',NULL,'Rhodes',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 75,'Wombat',NULL,'LaCasse',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 76,'Wombat',NULL,'Chase',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 77,'Wombat',NULL,'keene',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 78,'Wombat',NULL,'Huff',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 79,'Wombat',NULL,'Irwin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 80,'Wombat',NULL,'Conway',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 81,'Wombat',NULL,'page','d','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 82,'Wombat',NULL,'Webster',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 83,'Wombat',NULL,'Garrow',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 84,'Wombat',NULL,'Roy','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 85,'Wombat',NULL,'Gifford','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 86,'Wombat',NULL,'LeBlanc',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 87,'Wombat',NULL,'Quintin','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 88,'Wombat',NULL,'Anderson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 89,'Wombat',NULL,'Couch',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 90,'Wombat',NULL,'Gove',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 91,'Wombat',NULL,'Grass',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 92,'Wombat',NULL,'Domina',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 93,'Wombat',NULL,'Barney','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 94,'Wombat',NULL,'Royer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 95,'Wombat',NULL,'Wheeler',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 96,'Wombat',NULL,'Lapan',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 97,'Wombat',NULL,'Martell','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Roxbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 98,'Wombat',NULL,'Deutschbein',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 99,'Wombat',NULL,'maston',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 100,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all select 101,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all select 102,'Wombat',NULL,'Inman',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 103,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 104,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all select 105,'Wombat',NULL,'Putney',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 106,'Wombat',NULL,'Russo',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 107,'Wombat',NULL,'Davis',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 108,'Wombat',NULL,'Smith',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 109,'Wombat',NULL,'Bancroft',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 110,'Wombat',NULL,'Nolan','none','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 111,'Wombat',NULL,'LeBlanc','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 112,'Wombat',NULL,'LeBlanc','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 113,'Wombat',NULL,'follert','k','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 114,'Wombat',NULL,'Demar',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 115,'Wombat',NULL,'beauchemin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 116,'Wombat',NULL,'Doyon',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 117,'Wombat',NULL,'Beyor','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 118,'Wombat',NULL,'Bearce','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Westmore',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 119,'Wombat',NULL,'Millard','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL UNION all select 120,'Wombat',NULL,'Fitzpatrick',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','North Duxbury', NULL,NULL,NULL,NULL,NULL,NULL SET IDENTITY_INSERT survey.FirewoodLottery OFF -- set identity insert on for towns SET IDENTITY_INSERT survey.FirewoodTowns ON -- entering sample data into lottery table INSERT INTO [survey].[FirewoodTowns] ([FirewoodTowns_id] ,[Town] ,[District] ,[Permits] ,[Forest] ,[alternates] ,[PermitsCountdown] ,[AlternatesCountdown])select 14,'Plymouth','1',15,'Coolidge SF',5,15,0 union allselect 15,'Shrewsbury','2',8,'Coolidge SF',5,8,0 union allselect 16,'North Duxbury','3',10,'Camels Hump SF',10,10,0 union allselect 17,'Orange','4',10,'Groton SF',0,0,0 union allselect 18,'Roxbury','4',3,'Roxbury SF',0,3,0 union allselect 19,'Johnson','4',5,'Mt Mansfield SF',0,5,0 union allselect 20,'Groton','5',15,'Groton SF',10,0,0 union allselect 21,'Westmore','5',15,'Willoughby SF',10,15,0 SET IDENTITY_INSERT survey.FirewoodTowns Off[/code]Tue, 12 Mar 2013 03:32:59 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxThat's great and I'll take a look at your suggested change. However the real problem I'm having is the procedure where I'm trying to award the lots. That's in a separate post. You can find the first one I did, without cursors, in my first post. You can find the version of it I did with cursors, in my uhm, third? post.The stuff I just posted was the setup for being able to try out the other procedure.Mon, 11 Mar 2013 11:19:31 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxWell that was close. There were still a few issues but I was able to muddle my way through. (the insert issue was the very last one on the list had too many NULLs at the end.Now that I could actually see what was going on this whole procedure can be made about a million times more simple. Basically all you were doing was getting all the towns, then looping through them one at a time and updating the PermitsCountdown to the value in Permits where the PermitsCountdown <> Permits. You can do this in a single update statement with no need at all for a cursor.Unless I am missing something I think you could change your entire sp_ResetPermitsCountdown proc to be as simple as:[code]CREATE PROCEDURE [survey].[sp_ResetPermitsCountdown] AS update [survey].[FirewoodTowns] set PermitsCountdown = Permits where PermitsCountdown <> Permits[/code]Also, you really should not use the "sp_" prefix for your procedure names. SQL server will first think is a system stored proc.Mon, 11 Mar 2013 10:40:16 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxI apologize for my fumbling attempts, I've never tried to do anything like this before, and several of the commands are unfamiliar to me. Here is another try at the dll. I have one problem in that it won't populate the sample data and I don't know why it won't. It throws this error: Msg 205, Level 16, State 1, Line 7All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.I've double checked (and fixed some problems). When I count the fields in the insert, compared to those in the creates and the sample data lists, they all appear to be in agreement as to the number of fields.[code="sql"]Use TestingSetupTempgo CREATE SCHEMA survey AUTHORIZATION dbo-- creating the two tablesCREATE TABLE [survey].[FirewoodLottery]( [FirewoodLottery_id] [int] IDENTITY(1,1) NOT NULL, [Firstname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MiddleInitial] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Lastname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Suffix] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [state] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [zip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [phone] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [email] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [postdate] [datetime] NULL, [ip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [choice1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [choice2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [assigned] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [status] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [sortindex] [int] NULL, [choice] [int] NULL, [Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [possible_dup] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]goCREATE TABLE [survey].[FirewoodTowns]( [FirewoodTowns_id] [int] IDENTITY(1,1) NOT NULL, [Town] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Permits] [int] NULL, [Forest] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [alternates] [int] NULL, [PermitsCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_PermitsCountdown] DEFAULT ((0)), [AlternatesCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_AlternatesCountdown] DEFAULT ((0))) ON [PRIMARY]go-- creating the stored procedures referencedCREATE PROCEDURE [survey].[sp_GetFirewoodLotteryTowns] ASBEGINSelect Town, Permits, alternates, PermitsCountdown, AlternatesCountdownfrom survey.FirewoodTownsENDgoCREATE PROCEDURE [survey].[sp_GetRandomizedApplicants] ASBEGINselect TOP 800 FirewoodLottery_id ,choice1 ,choice2FROM survey.FirewoodLotteryORDER BY NEWID()ENDgoCREATE PROCEDURE [survey].[sp_ResetPermitsCountdown] ASBEGINDeclare @LotteryTownName as varchar(40)Declare @NumberOfPermits as intDeclare @NumberOfAlternates as intDeclare @CountdownOfPermits as intDeclare @CountdownOfAlternates as intDeclare @LoopCounter as intDeclare @RecordCount as intset nocount oncreate table #LotteryTowns ( Town varchar(40), Permits int, Alternates int, PermitsCountdown int, AlternatesCountdown int )INSERT INTO #LotteryTowns exec survey.sp_GetFirewoodLotteryTowns select @RecordCount = count(town) from #LotteryTownsdeclare cur cursor for select Town, Permits, Alternates, PermitsCountdown, AlternatesCountdown from #LotteryTowns open cur set @LoopCounter = 0/* Start loop here */ while @LoopCounter < @RecordCount begin fetch next from cur into @LotteryTownName, @NumberOfPermits, @NumberOfAlternates, @CountdownOfPermits, @CountdownOfAlternates; if @CountdownOfPermits <> @NumberOfPermits update [survey].[FirewoodTowns] set PermitsCountdown = @NumberOfPermits where Town = @LotteryTownName SET @LoopCounter = @LoopCounter + 1;/* End loop here */ end close cur deallocate cur drop table #LotteryTownsENDgo-- set identity insert on for lotterySET IDENTITY_INSERT survey.FirewoodLottery ON-- entering sample data into lottery table INSERT INTO [survey].[FirewoodLottery] ( [FirewoodLottery_ID] ,[Firstname] ,[MiddleInitial] ,[Lastname] ,[Suffix] ,[address] ,[city] ,[state] ,[zip] ,[phone] ,[email] ,[postdate] ,[ip] ,[choice1] ,[choice2] ,[assigned] ,[status] ,[District] ,[sortindex] ,[choice] ,[Notes] ,[possible_dup])select 1,'Wombat',NULL,'Champine',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union allselect 2,'Wombat',NULL,'Beaudry',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 3,'Wombat',NULL,'Couture',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 4,'Wombat',NULL,'VanHorn',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 5,'Wombat',NULL,'Roach',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 6,'Wombat',NULL,'Relation',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 7,'Wombat',NULL,'LaRose',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 8,'Wombat',NULL,'Deuso',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 9,'Wombat',NULL,'Valley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 10,'Wombat',NULL,'Beaudoin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 11,'Wombat',NULL,'VanHorn',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 12,'Wombat',NULL,'Robinson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 13,'Wombat',NULL,'Green',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 14,'Wombat',NULL,'Wheeler',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 15,'Wombat',NULL,'Trombley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 16,'Wombat',NULL,'Trombley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 17,'Wombat',NULL,'Compo',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 18,'Wombat',NULL,'Kipp',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 19,'Wombat',NULL,'Auger',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 20,'Wombat',NULL,'Auger',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 21,'Wombat',NULL,'Elliott','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 22,'Wombat',NULL,'Morse',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 23,'Wombat',NULL,'Morse',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 24,'Wombat',NULL,'Beauchemin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 25,'Wombat',NULL,'Curtis',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 26,'Wombat',NULL,'charron','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 27,'Wombat',NULL,'Barnett','IV','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 28,'Wombat',NULL,'Duke',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 29,'Wombat',NULL,'Gerrish',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 30,'Wombat',NULL,'charron',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 31,'Wombat',NULL,'Smith',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 32,'Wombat',NULL,'Gonyaw','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 33,'Wombat',NULL,'Gonyaw','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 34,'Wombat',NULL,'charron',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 35,'Wombat',NULL,'charron',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 36,'Wombat',NULL,'West',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 37,'Wombat',NULL,'Gerrish','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 38,'Wombat',NULL,'Clark','G','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 39,'Wombat',NULL,'Palmer','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 40,'Wombat',NULL,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 41,'Wombat',NULL,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 42,'Wombat',NULL,'Moser',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 43,'Wombat',NULL,'beyor',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union allselect 44,'Wombat',NULL,'richart','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Roxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 45,'Wombat',NULL,'Tighe',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 46,'Wombat',NULL,'Abare',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 47,'Wombat',NULL,'Willette',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 48,'Wombat',NULL,'Hinman',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 49,'Wombat',NULL,'Barnett','111','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 50,'Wombat',NULL,'Ware',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 51,'Wombat',NULL,'Ware',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 52,'Wombat',NULL,'Hackett',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 53,'Wombat',NULL,'Drinkwater',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 54,'Wombat',NULL,'Hale',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 55,'Wombat',NULL,'John',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 56,'Wombat',NULL,'John',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 57,'Wombat',NULL,'John',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 58,'Wombat',NULL,'alexander',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 59,'Wombat',NULL,'rogers',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 60,'Wombat',NULL,'Webster','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 61,'Wombat',NULL,'Celley',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 62,'Wombat',NULL,'Gerrish','Sr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 63,'Wombat',NULL,'Wilson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 64,'Wombat',NULL,'Tuthill',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 65,'Wombat',NULL,'hall',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 66,'Wombat',NULL,'Habel',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Roxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 67,'Wombat',NULL,'Barnett','Jean','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 68,'Wombat',NULL,'Hudson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 69,'Wombat',NULL,'Carleton','L','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 70,'Wombat',NULL,'Paige',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 71,'Wombat',NULL,'Brown',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 72,'Wombat',NULL,'Rhodes',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 73,'Wombat',NULL,'Wyckoff',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 74,'Wombat',NULL,'Rhodes',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 75,'Wombat',NULL,'LaCasse',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 76,'Wombat',NULL,'Chase',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 77,'Wombat',NULL,'keene',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 78,'Wombat',NULL,'Huff',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 79,'Wombat',NULL,'Irwin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 80,'Wombat',NULL,'Conway',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 81,'Wombat',NULL,'page','d','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 82,'Wombat',NULL,'Webster',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 83,'Wombat',NULL,'Garrow',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 84,'Wombat',NULL,'Roy','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 85,'Wombat',NULL,'Gifford','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 86,'Wombat',NULL,'LeBlanc',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 87,'Wombat',NULL,'Quintin','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 88,'Wombat',NULL,'Anderson',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 89,'Wombat',NULL,'Couch',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 90,'Wombat',NULL,'Gove',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 91,'Wombat',NULL,'Grass',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 92,'Wombat',NULL,'Domina',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 93,'Wombat',NULL,'Barney','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 94,'Wombat',NULL,'Royer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 95,'Wombat',NULL,'Wheeler',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 96,'Wombat',NULL,'Lapan',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 97,'Wombat',NULL,'Martell','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Roxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 98,'Wombat',NULL,'Deutschbein',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 99,'Wombat',NULL,'maston',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 100,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union allselect 101,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Shrewsbury',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union allselect 102,'Wombat',NULL,'Inman',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 103,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 104,'Wombat',NULL,'Teer',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Shrewsbury','Plymouth',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union allselect 105,'Wombat',NULL,'Putney',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 106,'Wombat',NULL,'Russo',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','North Duxbury','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 107,'Wombat',NULL,'Davis',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 108,'Wombat',NULL,'Smith',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 109,'Wombat',NULL,'Bancroft',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 110,'Wombat',NULL,'Nolan','none','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Groton',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 111,'Wombat',NULL,'LeBlanc','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 112,'Wombat',NULL,'LeBlanc','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 113,'Wombat',NULL,'follert','k','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Johnson','Johnson',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 114,'Wombat',NULL,'Demar',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 115,'Wombat',NULL,'beauchemin',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Roxbury','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 116,'Wombat',NULL,'Doyon',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 117,'Wombat',NULL,'Beyor','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Groton','Orange',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 118,'Wombat',NULL,'Bearce','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Orange','Westmore',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 119,'Wombat',NULL,'Millard','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Plymouth','Shrewsbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL union allselect 120,'Wombat',NULL,'Fitzpatrick',NULL,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com','1/2/2013 0:00:00','111.11.111.11','Westmore','North Duxbury',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL-- set identity insert on for townsSET IDENTITY_INSERT survey.FirewoodTowns ON-- entering sample data into lottery tableINSERT INTO [survey].[FirewoodTowns] ([FirewoodTowns_id] ,[Town] ,[District] ,[Permits] ,[Forest] ,[alternates] ,[PermitsCountdown] ,[AlternatesCountdown])select 14,'Plymouth','1',15,'Coolidge SF',5,15,0 union allselect 15,'Shrewsbury','2',8,'Coolidge SF',5,8,0 union allselect 16,'North Duxbury','3',10,'Camels Hump SF',10,10,0 union allselect 17,'Orange','4',10,'Groton SF',0,0,0 union allselect 18,'Roxbury','4',3,'Roxbury SF',0,3,0 union allselect 19,'Johnson','4',5,'Mt Mansfield SF',0,5,0 union allselect 20,'Groton','5',15,'Groton SF',10,0,0 union allselect 21,'Westmore','5',15,'Willoughby SF',10,15,0[/code]Mon, 11 Mar 2013 10:11:56 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxNice attempt at posting ddl and sample data. I would recommend you try running your code prior to posting. There are literally hundreds of issues with what you posted. Some are reasonably simple to fix (adding a GO between procs) but others are just too much to deal with. Your insert into FirewoodLottery has more things wrong than it has right. If you can clean this up so it is usable we can have a crack at this.Mon, 11 Mar 2013 08:34:28 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxHere's my attempt at providing enough information so you can help. :)I wasn't sure how to give you all the necessary code, so I'm dropping it all in at the end of my description here. I also, after I'd posted last week, gone back and tried redoing this as a cursor in the hope that it was simply my unfamiliarity that might be at issue, and I'm having the same problems even with the cursor. Since I've already posted my first attempt without the cursor above, I'll only add the second attempt with the cursor after all the set up code at the bottom.Every year there's a lottery held to award the right to harvest on state-owned firewood lots to citizens. There is a web application they apply through (I did not write it, I didn't design any of this, I inherited it when a coworker suddenly passed away last year). The old code was using cold fusion and Microsoft Access with just two tables on an SQL Backend. I'm trying to update this so that I don't have to go through the laborious Access process my predecessor had set up without having to rewrite everything he did (we're not replacing the cold fusion, yet for instance, it works well enough for now). So the task at hand is to set up the code that will award the lots appropriately.There are a specific number of lots awarded in different towns. The towns and the numbers of lots available changes every year. There are also specific numbers of alternates awarded afterwards. I was intending to simply create a second process the mirrored the first to do the alternate awards. I'm hoping that the comments in the code will make it clear what the process is, but I'm concerned that I might be bogging this down, so if you need me to outline the lottery procedure, I can do that, too, just let me know.[code="sql"]-- creating the two tablesCREATE TABLE [survey].[FirewoodLottery]( [FirewoodLottery_id] [int] IDENTITY(1,1) NOT NULL, [Firstname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MiddleInitial] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Lastname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Suffix] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [state] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [zip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [phone] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [email] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [postdate] [datetime] NULL, [ip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [choice1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [choice2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [assigned] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [status] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [sortindex] [int] NULL, [choice] [int] NULL, [Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [possible_dup] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]CREATE TABLE [survey].[FirewoodTowns]( [FirewoodTowns_id] [int] IDENTITY(1,1) NOT NULL, [Town] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Permits] [int] NULL, [Forest] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [alternates] [int] NULL, [PermitsCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_PermitsCountdown] DEFAULT ((0)), [AlternatesCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_AlternatesCountdown] DEFAULT ((0))) ON [PRIMARY]-- creating the stored procedures referencedCREATE PROCEDURE [survey].[sp_GetFirewoodLotteryTowns] ASBEGINSelect Town, Permits, alternates, PermitsCountdown, AlternatesCountdownfrom survey.FirewoodTownsENDCREATE PROCEDURE [survey].[sp_GetRandomizedApplicants] ASBEGINselect TOP 800 FirewoodLottery_id ,choice1 ,choice2FROM survey.FirewoodLotteryORDER BY NEWID()ENDCREATE PROCEDURE [survey].[sp_ResetPermitsCountdown] ASBEGINDeclare @LotteryTownName as varchar(40)Declare @NumberOfPermits as intDeclare @NumberOfAlternates as intDeclare @CountdownOfPermits as intDeclare @CountdownOfAlternates as intDeclare @LoopCounter as intDeclare @RecordCount as intset nocount oncreate table #LotteryTowns ( Town varchar(40), Permits int, Alternates int, PermitsCountdown int, AlternatesCountdown int )INSERT INTO #LotteryTowns exec survey.sp_GetFirewoodLotteryTowns select @RecordCount = count(town) from #LotteryTownsdeclare cur cursor for select Town, Permits, Alternates, PermitsCountdown, AlternatesCountdown from #LotteryTowns open cur set @LoopCounter = 0/* Start loop here */ while @LoopCounter < @RecordCount begin fetch next from cur into @LotteryTownName, @NumberOfPermits, @NumberOfAlternates, @CountdownOfPermits, @CountdownOfAlternates; if @CountdownOfPermits <> @NumberOfPermits update [survey].[FirewoodTowns] set PermitsCountdown = @NumberOfPermits where Town = @LotteryTownName SET @LoopCounter = @LoopCounter + 1;/* End loop here */ end close cur deallocate cur drop table #LotteryTownsEND-- set identity insert on for lotterySET IDENTITY_INSERT survey.FirewoodLottery ON-- entering sample data into lottery table INSERT INTO [survey].[FirewoodLottery] (ID, DateValue, Value, YearValue, Monthvalue) [Firstname] ,[MiddleInitial] ,[Lastname] ,[Suffix] ,[address] ,[city] ,[state] ,[zip] ,[phone] ,[email] ,[postdate] ,[ip] ,[choice1] ,[choice2] ,[assigned] ,[status] ,[District] ,[sortindex] ,[choice] ,[Notes] ,[possible_dup]select 1,'Wombat',,'Champine',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,'Dupe',,,,,union allselect 2,'Wombat',,'Beaudry',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 3,'Wombat',,'Couture',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 4,'Wombat',,'VanHorn',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union allselect 5,'Wombat',,'Roach',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Johnson',,,,,,,union allselect 6,'Wombat',,'Relation',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 7,'Wombat',,'LaRose',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 8,'Wombat',,'Deuso',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','North Duxbury',,,,,,,union allselect 9,'Wombat',,'Valley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 10,'Wombat',,'Beaudoin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 11,'Wombat',,'VanHorn',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union allselect 12,'Wombat',,'Robinson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 13,'Wombat',,'Green',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 14,'Wombat',,'Wheeler',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union allselect 15,'Wombat',,'Trombley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 16,'Wombat',,'Trombley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 17,'Wombat',,'Compo',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 18,'Wombat',,'Kipp',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 19,'Wombat',,'Auger',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 20,'Wombat',,'Auger',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 21,'Wombat',,'Elliott','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Westmore',,,,,,,union allselect 22,'Wombat',,'Morse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union allselect 23,'Wombat',,'Morse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union allselect 24,'Wombat',,'Beauchemin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union allselect 25,'Wombat',,'Curtis',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','North Duxbury',,,,,,,union allselect 26,'Wombat',,'charron','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union allselect 27,'Wombat',,'Barnett','IV','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 28,'Wombat',,'Duke',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 29,'Wombat',,'Gerrish',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 30,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union allselect 31,'Wombat',,'Smith',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 32,'Wombat',,'Gonyaw','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 33,'Wombat',,'Gonyaw','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 34,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union allselect 35,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union allselect 36,'Wombat',,'West',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 37,'Wombat',,'Gerrish','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 38,'Wombat',,'Clark','G','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 39,'Wombat',,'Palmer','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 40,'Wombat',,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 41,'Wombat',,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 42,'Wombat',,'Moser',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union allselect 43,'Wombat',,'beyor',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,'Dupe',,,,,union allselect 44,'Wombat',,'richart','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Roxbury',,,,,,,union allselect 45,'Wombat',,'Tighe',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 46,'Wombat',,'Abare',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union allselect 47,'Wombat',,'Willette',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union allselect 48,'Wombat',,'Hinman',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 49,'Wombat',,'Barnett','111','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union allselect 50,'Wombat',,'Ware',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 51,'Wombat',,'Ware',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 52,'Wombat',,'Hackett',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 53,'Wombat',,'Drinkwater',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 54,'Wombat',,'Hale',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 55,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 56,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 57,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 58,'Wombat',,'alexander',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union allselect 59,'Wombat',,'rogers',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union allselect 60,'Wombat',,'Webster','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Johnson',,,,,,,union allselect 61,'Wombat',,'Celley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 62,'Wombat',,'Gerrish','Sr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 63,'Wombat',,'Wilson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Plymouth',,,,,,,union allselect 64,'Wombat',,'Tuthill',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union allselect 65,'Wombat',,'hall',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 66,'Wombat',,'Habel',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Roxbury',,,,,,,union allselect 67,'Wombat',,'Barnett','Jean','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union allselect 68,'Wombat',,'Hudson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Orange',,,,,,,union allselect 69,'Wombat',,'Carleton','L','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 70,'Wombat',,'Paige',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union allselect 71,'Wombat',,'Brown',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 72,'Wombat',,'Rhodes',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 73,'Wombat',,'Wyckoff',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Johnson',,,,,,,union allselect 74,'Wombat',,'Rhodes',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 75,'Wombat',,'LaCasse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 76,'Wombat',,'Chase',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Groton',,,,,,,union allselect 77,'Wombat',,'keene',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Johnson',,,,,,,union allselect 78,'Wombat',,'Huff',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 79,'Wombat',,'Irwin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union allselect 80,'Wombat',,'Conway',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union allselect 81,'Wombat',,'page','d','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union allselect 82,'Wombat',,'Webster',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 83,'Wombat',,'Garrow',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 84,'Wombat',,'Roy','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union allselect 85,'Wombat',,'Gifford','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Westmore',,,,,,,union allselect 86,'Wombat',,'LeBlanc',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Shrewsbury',,,,,,,union allselect 87,'Wombat',,'Quintin','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union allselect 88,'Wombat',,'Anderson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 89,'Wombat',,'Couch',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union allselect 90,'Wombat',,'Gove',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union allselect 91,'Wombat',,'Grass',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 92,'Wombat',,'Domina',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union allselect 93,'Wombat',,'Barney','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 94,'Wombat',,'Royer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 95,'Wombat',,'Wheeler',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Westmore',,,,,,,union allselect 96,'Wombat',,'Lapan',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 97,'Wombat',,'Martell','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Roxbury',,,,,,,union allselect 98,'Wombat',,'Deutschbein',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union allselect 99,'Wombat',,'maston',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union allselect 100,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,'Dupe',,,,,union allselect 101,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,'Dupe',,,,,union allselect 102,'Wombat',,'Inman',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union allselect 103,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union allselect 104,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,'Dupe',,,,,union allselect 105,'Wombat',,'Putney',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 106,'Wombat',,'Russo',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Groton',,,,,,,union allselect 107,'Wombat',,'Davis',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 108,'Wombat',,'Smith',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union allselect 109,'Wombat',,'Bancroft',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 110,'Wombat',,'Nolan','none','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union allselect 111,'Wombat',,'LeBlanc','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Orange',,,,,,,union allselect 112,'Wombat',,'LeBlanc','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Orange',,,,,,,union allselect 113,'Wombat',,'follert','k','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union allselect 114,'Wombat',,'Demar',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union allselect 115,'Wombat',,'beauchemin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union allselect 116,'Wombat',,'Doyon',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Orange',,,,,,,union allselect 117,'Wombat',,'Beyor','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union allselect 118,'Wombat',,'Bearce','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Westmore',,,,,,,union allselect 119,'Wombat',,'Millard','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union allselect 120,'Wombat',,'Fitzpatrick',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','North Duxbury',,,,,,,,select -- set identity insert on for townsSET IDENTITY_INSERT survey.FirewoodTowns ON-- entering sample data into lottery tableINSERT INTO [survey].[FirewoodTowns] ([Town] ,[District] ,[Permits] ,[Forest] ,[alternates] ,[PermitsCountdown] ,[AlternatesCountdown)select 14,'Plymouth','1',15,'Coolidge SF',5,15,0 union allselect 15,'Shrewsbury','2',8,'Coolidge SF',5,8,0 union allselect 16,'North Duxbury','3',10,'Camels Hump SF',10,10,0 union allselect 17,'Orange','4',10,'Groton SF',0,0,0 union allselect 18,'Roxbury','4',3,'Roxbury SF',0,3,0 union allselect 19,'Johnson','4',5,'Mt Mansfield SF',0,5,0 union allselect 20,'Groton','5',15,'Groton SF',10,0,0 union allselect 21,'Westmore','5',15,'Willoughby SF',10,15,0[/code]Here's with the cursor:[code="sql"]CREATE PROCEDURE [survey].[sp_AwardFirewoodLots]ASBEGINDeclare @ApplicantID as INTDeclare @TownCount as INTDeclare @PermitsCount as INTDeclare @FirstChoice as NCHAR(50)Declare @SecondChoice as NCHAR(50)Declare @LoopCounter as intDeclare @RecordCount as intset nocount onexec [survey].[sp_ResetPermitsCountdown]create table #RandomizedApplicants (FirewoodLottery_id int,Choice1 varchar(40),Choice2 varchar(40))INSERT INTO #RandomizedApplicants exec survey.[sp_GetRandomizedApplicants]select @RecordCount = count(FirewoodLottery_id) from #RandomizedApplicantsdeclare cur cursorfor select FirewoodLottery_id, choice1, choice2 from #RandomizedApplicants open cur set @LoopCounter = 0/* Start loop here */ while @LoopCounter < @RecordCount begin fetch next from cur into @ApplicantID, @FirstChoice, @SecondChoice; SELECT @PermitsCount = [PermitsCountdown] FROM survey.FirewoodTowns WHERE [Town] = @FirstChoice IF @PermitsCount <> 0 BEGIN UPDATE survey.FirewoodLottery SET assigned = @FirstChoice ,notes = 'Won First Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101) WHERE FirewoodLottery_id = @ApplicantID SELECT @TownCount = @PermitsCount - 1 UPDATE survey.FirewoodTowns SET PermitsCountdown = @TownCount WHERE Town = @FirstChoice END ELSE BEGIN -- first choice is full so figure out if second choice is full SELECT @PermitsCount = [PermitsCountdown] FROM survey.FirewoodTowns WHERE [Town] = @SecondChoice IF @PermitsCount <> 0 BEGIN SELECT @TownCount = @PermitsCount - 1 UPDATE survey.FirewoodTowns SET PermitsCountdown = @TownCount WHERE Town = @SecondChoice UPDATE survey.FirewoodLottery SET assigned = @SecondChoice ,notes = 'Won Second Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101) WHERE FirewoodLottery_id = @ApplicantID END ELSE BEGIN -- report that both choices are full UPDATE survey.FirewoodLottery SET assigned = 'Nothing' ,notes = 'Both Choices Full ' + CONVERT(VARCHAR(15), GETDATE(), 101) WHERE FirewoodLottery_id = @ApplicantID END SET @LoopCounter = @LoopCounter + 1;/* End loop here */ end close cur deallocate curdrop table #RandomizedApplicants endEND[/code]Mon, 11 Mar 2013 07:39:46 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxGetting back to the problem at hand, I did a read of the code and I don't readily see the problem. I'll take another look after I get some shuteye.Sat, 09 Mar 2013 22:58:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]Jeff Moden (3/9/2013)[/b][hr][quote][b]Siobhan Perricone (3/8/2013)[/b][hr]I got asked to not use a cursor for a loop that I'm working on so I grabbed the code here:http://www.sql-server-performance.com/2004/operations-no-cursors/[/quote]To emphasize what Gail has stated, they should outlaw articles like the one you provided the link for. Probably most important is the fact that the author has produced absolutely NO performance stats on the two methods and, like many of us had in the early days until we tested, has bought into the myth that Temp Tables or Table Variables and While loops are more efficient than Cursors. A well written "firehose" cursor is just as effecient and easier to code than such alternatives. It's usually a total waste of time to convert Cursors to alternatives. You won't actually believe it until you prove it to yourself with code.Don't buy into the "Cursor Replacement Myth" of replacing cursors with While Loops and Temp Tables. It usually makes no difference. If you feel you must, just convert the cursor to a "firehose" cursor and call it a day.What you should buy into, however, is that there's usually a very high performance method to avoid RBAR (see my sig line below for definition) altogether. Even multiple set-based passes on a Temp Table will blow any While loop away whether in a Cursor or not.[/quote]I will third that opinion. This is an argument that I have heard time and again that a while loop is more efficient. Yuck.Here is a good article on comparing/ converting cursors to while loops and why the conversion just isn't what is advertised by that sql-server-performance article referenced above:[url]http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx[/url]Sat, 09 Mar 2013 08:24:29 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspx[quote][b]Siobhan Perricone (3/8/2013)[/b][hr]I got asked to not use a cursor for a loop that I'm working on so I grabbed the code here:http://www.sql-server-performance.com/2004/operations-no-cursors/[/quote]To emphasize what Gail has stated, they should outlaw articles like the one you provided the link for. Probably most important is the fact that the author has produced absolutely NO performance stats on the two methods and, like many of us had in the early days until we tested, has bought into the myth that Temp Tables or Table Variables and While loops are more efficient than Cursors. A well written "firehose" cursor is just as effecient and easier to code than such alternatives. It's usually a total waste of time to convert Cursors to alternatives. You won't actually believe it until you prove it to yourself with code.Don't buy into the "Cursor Replacement Myth" of replacing cursors with While Loops and Temp Tables. It usually makes no difference. If you feel you must, just convert the cursor to a "firehose" cursor and call it a day.What you should buy into, however, is that there's usually a very high performance method to avoid RBAR (see my sig line below for definition) altogether. Even multiple set-based passes on a Temp Table will blow any While loop away whether in a Cursor or not.Sat, 09 Mar 2013 07:32:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxJust worth noting, a while loop is just as much a cursor as an explicitly defined cursor. It just doesn't have the keyword. It's still a loop, it's still processing a row at a time, it's still slow.Sat, 09 Mar 2013 05:51:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxDon't apologize for your post. I've looked at the code you posted, it's just that there isn't enough there to really work with. Since we don't know the table structures or the data you are working with we can't setup a test environment on our systems to properly work the problem.As volunteers we rely on you to provide us the information we need to be able to help you. The more you can do to helps, the better help we can provide you. In addition, you get the benefit of tested code as well, not just something thrown together that might possibly be a solution to your problem.Sat, 09 Mar 2013 05:45:21 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxDidn't realize which forum I was on or that I was so new. Thanks for the tips, sorry for the inadequate post.Fri, 08 Mar 2013 07:22:57 GMTSiobhan Perriconehttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxWelcome to SSC. As it appears you are new here there are some things you need to do to helps help you and for you to get better answers to your problem.First, start by reading the first article I reference below in my signature block regarding asking for help. The instructions in that article will help with the information you need to post and how to post it.We really do need the DDL (CREATE TABLE) statement for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s) involved, and the expected results based on the sample data. Please note, we aren't asking for actual, confidential data as sample data. We just want data that is representative of your problem domain and the problem you are trying to solve.You do this and you will get good answers in return plus the code provided will be tested against the data you provided.Fri, 08 Mar 2013 07:15:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428562-338-1.aspxI got asked to not use a cursor for a loop that I'm working on so I grabbed the code here:http://www.sql-server-performance.com/2004/operations-no-cursors/And adjusted it to do what I need. It's not updating how I'm expecting it to. If I comment out the part where it subtracts one from the town count, then it treats everyone as if the first if statement were true. If I don't comment it out, then it treats everyone as if both the first and second are false because it is subtracting all the counts from the towncount and making it 0.There's probably some stupid syntax thing that I'm forgetting or just not realizing I need. I'm late getting this out, and I have staff getting calls about this lottery, so I was hoping I could get some help. Thanks for any suggestions you can make that'll help me find an avenue for pursuit. (sorry for the big indenting I can't seem to get it to stop doing that)[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [survey].[sp_AwardFirewoodLots]ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;exec [survey].[sp_ResetPermitsCountdown] -- declare all variables! DECLARE @iReturnCode INT ,@iNextRowId INT ,@iCurrentRowId INT ,@iLoopControl INT ,@ApplicantID INT ,@TownCount INT ,@PermitsCount INT ,@FirstChoice NCHAR(50) ,@SecondChoice NCHAR(50) -- Create randomized temp table of applicants --DROP TABLE #RandomizedApplicants SELECT @iLoopControl = 1 SELECT TOP 500 row_number() over (ORDER BY NEWID()) as iRowId ,FirewoodLottery_id ,choice1 ,choice2 INTO #RandomizedApplicants FROM survey.FirewoodLottery ORDER BY NEWID() -- Initialize variables! SELECT @iNextRowId = MIN(iRowId) FROM #RandomizedApplicants -- Make sure the table has data. IF ISNULL(@iNextRowId, 0) = 0 BEGIN SELECT 'No data in found in table!' RETURN END -- Retrieve the first row SELECT @iCurrentRowId = iRowId ,@ApplicantID = FirewoodLottery_id ,@FirstChoice = choice1 ,@SecondChoice = choice2 FROM #RandomizedApplicants WHERE iRowId = @iNextRowId -- start the main processing loop. WHILE @iLoopControl = 1 BEGIN -- This is where you perform your detailed row-by-row processing. -- figure out if the first choice is full SELECT @PermitsCount = [PermitsCountdown] FROM survey.FirewoodTowns WHERE [Town] = @FirstChoice IF @PermitsCount > 0 BEGIN UPDATE survey.FirewoodLottery SET assigned = @FirstChoice ,notes = 'Won First Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101) WHERE FirewoodLottery_id = @ApplicantID SELECT @TownCount = @PermitsCount - 1 UPDATE survey.FirewoodTowns SET PermitsCountdown = @TownCount WHERE Town = @FirstChoice END ELSE BEGIN -- first choice is full so figure out if second choice is full SELECT @PermitsCount = [PermitsCountdown] FROM survey.FirewoodTowns WHERE [Town] = @SecondChoice IF @PermitsCount > 0 BEGIN SELECT @TownCount = @PermitsCount - 1 UPDATE survey.FirewoodTowns SET PermitsCountdown = @TownCount WHERE Town = @SecondChoice UPDATE survey.FirewoodLottery SET assigned = @SecondChoice ,notes = 'Won Second Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101) WHERE FirewoodLottery_id = @ApplicantID END ELSE BEGIN -- report that both choices are full UPDATE survey.FirewoodLottery SET assigned = 'Nothing' ,notes = 'Both Choices Full ' + CONVERT(VARCHAR(15), GETDATE(), 101) WHERE FirewoodLottery_id = @ApplicantID END -- Reset looping variables. SELECT @iNextRowId = NULL -- get the next iRowId SELECT @iNextRowId = MIN(iRowId) FROM #RandomizedApplicants WHERE iRowId > @iCurrentRowId -- did we get a valid next row id? IF ISNULL(@iNextRowId, 0) = 0 BEGIN BREAK END -- get the next row. SELECT @iCurrentRowId = iRowId ,@ApplicantID = FirewoodLottery_id ,@FirstChoice = choice1 ,@SecondChoice = choice2 FROM #RandomizedApplicants WHERE iRowId = @iNextRowId END END RETURNENDGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO[/code]Fri, 08 Mar 2013 06:56:15 GMTSiobhan Perricone