Ok, strange one here, but I need to duplicate some data in my database. It's a SQL Server, and currently has a few thousand records in. I want to increase by a factor of 100, to get some stress-test results on the database as a whole.
I've used the following in Access to duplicate a set of data 50 times, and it's working well, but I'd love to know how to do something similar on the SQL box as a function. Can anyone help?
CODE
Public Sub DuplicateDataLOCATIONS()
Dim rs As DAO.Recordset, lngCounter As Long
Dim strLocation As String, strDesc As String, strType As String, strSiteID As String, strParent As String, SystemID As String, bnSysOrSubSys As Boolean
Dim strEquipClass As String, lngStatus As Long, strDrawingRef As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM LOCATION", dbOpenSnapshot, dbSeeChanges)
lngCounter = 0
For i = 1 To 50
rs.MoveFirst
Do Until rs.EOF
With rs
strLocation = .Fields("Location") & "-" & i
strDesc = Replace(.Fields("Description"), "'", "")
strType = .Fields("Type")
strSiteID = .Fields("SiteID") & "-" & i
strParent = .Fields("Parent") & "-" & i
strSystemID = .Fields("SystemID") & "-" & i
bnSysOrSubSys = .Fields("SysOrSubSys")
strEquipClass = Nz(.Fields("Equip_Class"), "")
lngStatus = Nz(.Fields("WTF_STATUS"), 0)
strDrawingRef = Replace(Nz(.Fields("DRAWING_REF"), ""), "'", "")
End With
strSQL = "INSERT INTO LOCATION(LOCATION, DESCRIPTION, TYPE, SITEID, PARENT, SYSTEMID, SysOrSubSys, WTF_STATUS, DRAWING_REF, EQUIP_CLASS) VALUES ('" & _
strLocation & "', '" & strDesc & "', '" & strType & "', '" & strSiteID & "', '" & strParent & "', '" & strSystemID & _
"', " & bnSysOrSubSys & ", '" & strEquipClass & "', " & lngStatus & ", '" & strDrawingRef & "')"
CurrentDb.Execute strSQL
lngCounter = lngCounter + 1
rs.MoveNext
Loop
Next i
MsgBox lngCounter & "records added in total!"
End Sub
Dim rs As DAO.Recordset, lngCounter As Long
Dim strLocation As String, strDesc As String, strType As String, strSiteID As String, strParent As String, SystemID As String, bnSysOrSubSys As Boolean
Dim strEquipClass As String, lngStatus As Long, strDrawingRef As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM LOCATION", dbOpenSnapshot, dbSeeChanges)
lngCounter = 0
For i = 1 To 50
rs.MoveFirst
Do Until rs.EOF
With rs
strLocation = .Fields("Location") & "-" & i
strDesc = Replace(.Fields("Description"), "'", "")
strType = .Fields("Type")
strSiteID = .Fields("SiteID") & "-" & i
strParent = .Fields("Parent") & "-" & i
strSystemID = .Fields("SystemID") & "-" & i
bnSysOrSubSys = .Fields("SysOrSubSys")
strEquipClass = Nz(.Fields("Equip_Class"), "")
lngStatus = Nz(.Fields("WTF_STATUS"), 0)
strDrawingRef = Replace(Nz(.Fields("DRAWING_REF"), ""), "'", "")
End With
strSQL = "INSERT INTO LOCATION(LOCATION, DESCRIPTION, TYPE, SITEID, PARENT, SYSTEMID, SysOrSubSys, WTF_STATUS, DRAWING_REF, EQUIP_CLASS) VALUES ('" & _
strLocation & "', '" & strDesc & "', '" & strType & "', '" & strSiteID & "', '" & strParent & "', '" & strSystemID & _
"', " & bnSysOrSubSys & ", '" & strEquipClass & "', " & lngStatus & ", '" & strDrawingRef & "')"
CurrentDb.Execute strSQL
lngCounter = lngCounter + 1
rs.MoveNext
Loop
Next i
MsgBox lngCounter & "records added in total!"
End Sub
