I need to grab a random 25000 records from a database, no dups.. I found
some code online and modified it, but it is to slow... Works great for just
a few records..
The query..
SELECT DISTINCTROW old_table.Name INTO new_table
FROM old_table
WHERE (((InStr(1,rndSet(25500,'old_table'),("," & [id] & ",")))<>0));
Here is The function..
Function rndSet(maxNumber, tablename) As String
Dim dbs As Database, rst As Recordset
Dim strSQL As String
Dim str
Dim cnt As Long
Dim cnt1 As Long
Dim rndMax As Long
Dim RndNumber As Long
Dim str2
Dim cnt3
Set dbs = CurrentDb
strSQL = "SELECT * FROM " & tablename
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
cnt = CLng(rst.RecordCount)
cnt3 = CLng(maxNumber)
If cnt3 > cnt Then
cnt1 = CLng(rst.RecordCount)
Else
cnt1 = cnt3
End If
rndMax = cnt
str = ","
str2 = "'"
Do Until cnt1 = 0
Randomize
RndNumber = Int(Rnd * rndMax)
If (InStr(str2, CStr(RndNumber)) = 0) And RndNumber <> 0 Then
str2 = str2 & RndNumber & "','"
cnt1 = cnt1 - 1
rst.MoveFirst
rst.Move RndNumber
If cnt1 = 0 Then
str = str & rst("id") & ","
Exit Do
Else
str = str & rst("id") & ","
End If
End If
Loop
rndSet = str
rst.Close
Set dbs = Nothing
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
