This is a good idea, although you do not need a cursor, just do a select
into the table as a whole.
After the table is created issue:
insert into #B (
K,
RandomNumber
)
select
@keyValue,
rand()
from tableA

Brian Freeman
Carnegie Technologies/Bluewave Computing
(770) 916-0595 ext. 415
www.carnegie.com
www.bluewave-computing.com


-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x56442687.[Email address protected]
Sent: Thursday, February 24, 2005 11:37 AM
To: LazyDBA Discussion
Subject: RE: Query Help - Random records

A way could be the following:

you have the source table looking like this
table A
(
K <keytype> primary key,
other columns...
)


you create a temporary
table #B
(
K <keytype> not null,
RandomNumber float not null
)


now use a cursor for looping through every record in table A and do:
begin
fetch next from cursorEveryRowFromTableA into @keyValue
-- key column of current record in cursor


insert into #B (
K,
RandomNumber
)
select
@keyValue,
rand()
end


then you create an index on #B (RandomNumber)

then you do:
select top <(number of total Rows) * 0.2> K
from #B
order by RandomNumber

which is delivering keys randomly selected.


Maybe there are more elegant ways to do it but this one should work.


regards FZ


-----Original Message-----
From: Mindy Riddick
[mailto:mssqldba-ezmlmshield-x33149489.[Email address protected]
Sent: Donnerstag, 24. Februar 2005 17:02
To: LazyDBA Discussion
Subject: Query Help - Random records


Hi everyone,

I need to create a query to randomly select 20% of the records in my
database. I am familiar with randomly creating numbers using RAND() and
am researching possible solutions. If anyone has any suggestions, I
would greatly appreciate it.

Mindy


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]




MS Sql Server LazyDBA home page