InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 16:34:32 GMT20http://www.sqlservercentral.com/Forums/Topic897847-391-1.aspxWow!Thanks for the help guys... you gave me good insights!I'll keep you postedThanksTue, 06 Apr 2010 20:48:58 GMTsteve-lauzonhttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspxI made a very similar one before I re-read it and thought that he needed those numbers to exist in another table. Maybe he does, maybe not. [code="sql"]DECLARE @T TABLE( Region int, MinV int, MaxV int, NumToGen int)INSERT INTO @T(Region, MinV, MaxV, NumToGen)VALUES(1,0,5000,100),(2,1700,2300,50);WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N), t2 AS (SELECT 1 N FROM t1 x, t1 y), t3 AS (SELECT 1 N FROM t2 x, t2 y), t4 AS (SELECT 1 N FROM t3 x, t3 y), Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y), RNG AS (SELECT Region, NumToGen, MinV + ABS(CHECKSUM(NEWID()))%(MaxV-MinV) RV FROM @t ) SELECT Region, N, RVFROM RNG, TallyWHERE N <=NumToGenORDER BY Region, N[/code]Tue, 06 Apr 2010 16:10:38 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspxBased on my understanding of your requirements:[code="sql"]declare @t table ( RegionName varchar(10) not null primary key clustered,[Min] int not null,[Max] int not null,[NumberToGenerate] int not null)insert into @tselect 'Region 1', 0, 5000, 15 union allselect 'Region 2', 1700, 2300, 25 union allselect 'Region 3', 13, 55, 14declare @maxNumberToGenerate intselect @maxNumberToGenerate = max(NumberToGenerate) from @tdeclare @num table (number int not null primary key clustered)insert into @numselect numberfrom -- Number table function F_TABLE_NUMBER_RANGE available here -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 dbo.F_TABLE_NUMBER_RANGE(1,@maxNumberToGenerate)order by numberselect b.RegionName, a.number, -- Generate random number in range of Min to Max RandNo = (convert(bigint,convert(varbinary(7),newid()))%([max]-[Min]+1))+[Min]from @num a join @t b on a.number <= NumberToGenerateorder by b.RegionName, a.number[/code]Results:[code="plain"]RegionName number RandNo ---------- ----------- -------------------- Region 1 1 4725Region 1 2 2763Region 1 3 1661Region 1 4 794Region 1 5 3244Region 1 6 1766Region 1 7 3613Region 1 8 4361Region 1 9 585Region 1 10 447Region 1 11 4242Region 1 12 336Region 1 13 230Region 1 14 1029Region 1 15 1149Region 2 1 1710Region 2 2 1744Region 2 3 1762Region 2 4 1953Region 2 5 1996Region 2 6 1909Region 2 7 1993Region 2 8 1864Region 2 9 2218Region 2 10 2044Region 2 11 1890Region 2 12 2004Region 2 13 1723Region 2 14 1944Region 2 15 2132Region 2 16 1769Region 2 17 1848Region 2 18 1878Region 2 19 2193Region 2 20 1858Region 2 21 2293Region 2 22 2124Region 2 23 1787Region 2 24 1714Region 2 25 1844Region 3 1 41Region 3 2 48Region 3 3 26Region 3 4 18Region 3 5 18Region 3 6 52Region 3 7 41Region 3 8 33Region 3 9 25Region 3 10 22Region 3 11 19Region 3 12 33Region 3 13 19Region 3 14 52(54 row(s) affected)[/code]Tue, 06 Apr 2010 15:44:43 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspx[quote][b]steve-lauzon (4/6/2010)[/b][hr]2. Thanks also for the link. I will do so in my future post.[/quote]Heh... if you do it on this post, I might be able to provide a high speed alternative to the cursor code you're currently using. ;-)Tue, 06 Apr 2010 15:37:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspxThanks for the answers1. Yes it needs to be generated randomly. It's a statistical application and the first rule is everything has to be generated randomly2. Thanks also for the link. I will do so in my future post.3. I will check my other queries to make sure that they are optimized.Thanks againTue, 06 Apr 2010 15:35:18 GMTsteve-lauzonhttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspx[quote][b]steve-lauzon (4/6/2010)[/b][hr]Hi all... this is my first post in the forums. I hope I'm following the guidelinesI need help!I got a table that contains a list of several regions (RegionName, Min, Max, NumberToGenerate)Ex : Region 1, 0, 5000, 100 Region 2, 1700, 2300, 50I need to generate randomly X numbers (numbertogenerate) within the specified scope (between min and max) for each regionAfter the number is generated, I have to check in another table to see if that number exist and if it does, add it to a ListedTable and if not, to a NonListed table. There are several other conditions to check but for the sake of this example, I'll keep it simple :-)I have to loop within the specified region until I reach the listed numbers to generate. When reached, I move to the next regionI tried using cursors which works fine but really slow.I have no clue how to conceive that procedure using batchs - I'm missing something.I know you have limited information, but I would appreciate some hints. I can give more info if neededThanks[/quote]Ummmm.... why does this need to be done randomly? I'm asking for two reasons... does it really need to be done randomly and there are several methods to pull this off randomly. Also, take a peek at the first link in my signature line below... it would help us help you a whole lot faster.Tue, 06 Apr 2010 15:27:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspxI do think using a cursor is the right strategy.Post suggest there are a number of secondary processes and tables involved, may be the process is slow because some of the other queries e.g. check if number exists in other table - are slow.I would work in making sure all queries involved perform well.Tue, 06 Apr 2010 14:26:13 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic897847-391-1.aspxHi all... this is my first post in the forums. I hope I'm following the guidelinesI need help!I got a table that contains a list of several regions (RegionName, Min, Max, NumberToGenerate)Ex : Region 1, 0, 5000, 100 Region 2, 1700, 2300, 50I need to generate randomly X numbers (numbertogenerate) within the specified scope (between min and max) for each regionAfter the number is generated, I have to check in another table to see if that number exist and if it does, add it to a ListedTable and if not, to a NonListed table. There are several other conditions to check but for the sake of this example, I'll keep it simple :-)I have to loop within the specified region until I reach the listed numbers to generate. When reached, I move to the next regionI tried using cursors which works fine but really slow.I have no clue how to conceive that procedure using batchs - I'm missing something.I know you have limited information, but I would appreciate some hints. I can give more info if neededThanks Tue, 06 Apr 2010 13:20:14 GMTsteve-lauzon