Login  | |
|
 |
RE: Insert a recordset object into a table - 2/10/2006 4:26:08 AM
|
|
 |
|
| |
ebgreen
Posts: 5246
Score: 31
Joined: 7/12/2005
Status: offline
|
I'm obviously not the best person to answer this because I would just use the data in the recordset to generate an insert query to run against the second table and I'm sure that there must be a better way.
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: Insert a recordset object into a table - 2/10/2006 4:45:39 AM
|
|
|
|
| |
ebgreen
Posts: 5246
Score: 31
Joined: 7/12/2005
Status: offline
|
So then you just need to write an insert query using the values in the dataset that you have but running against the database and table that you want to insert into. Post some of your code and I'll see if I can work an example into it.
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: Insert a recordset object into a table - 2/10/2006 4:52:34 AM
|
|
|
|
| |
skione
Posts: 40
Score: 0
Joined: 10/1/2005
Status: offline
|
so there is no way to copy the whole recordset object with having to loop through it one at a time?\
this is what I have
on error resume next
Dim sQuery, sResults
Dim oADO, oRS
Const ForReading = 1
strCurrentDir = Left(WScript.ScriptFullName,InstrRev(WScript.ScriptFullName,"\"))
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("puterlist.file", ForReading)
strText = objTextFile.ReadAll
objTextFile.Close
arrComputers = Split(strText, vbCrLf)
sQuery = "SELECT * FROM alerts where error_code='29050'AND TIME>='1/1/2006'AND TIME<'2/1/2006'"
for each strserver in arrComputers
Set oADO = CreateObject("ADODB.Connection")
connection = "uid=sa;pwd=*****;driver={SQL Server};server=" & strServer & ";database=pharos"
oADO.Open (connection)
Set oRS = oADO.Execute(squery)
If Not oRS.EOF Then GetResults = oRS.GetString(2,,",",vbCRLF,"")
oRS.Close : Set oRS = Nothing
oADO.Close : Set oADO = Nothing
If Not IsEmpty(GetResults) Then
Call WriteResults(strServer & ".txt", GetResults)
Else
Call WriteResults(strServer & ".txt", "No records returned." & strComputer)
End If
'would like to add an insert query here that will insert the results of the above recordset into a different table on a different server
next
Sub WriteResults(filename,results)
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(filename, True) '''overwrite existing
oFile.Write results
oFile.Close : Set oFile = Nothing
Set oFSO = Nothing
End Sub
|
|
| |
|
|
|
|
RE: Insert a recordset object into a table - 2/10/2006 5:10:48 AM
|
|
|
|
| |
ebgreen
Posts: 5246
Score: 31
Joined: 7/12/2005
Status: offline
|
quote:
so there is no way to copy the whole recordset object with having to loop through it one at a time?
See...now we're back to me not being the best choice. That may be possible. I just don't know. To do it my (potentially inefficient) way:
on error resume next
Dim sQuery, sResults
Dim oADO, oRS
Const ForReading = 1
strCurrentDir = Left(WScript.ScriptFullName,InstrRev(WScript.ScriptFullName,"\"))
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("puterlist.file", ForReading)
strText = objTextFile.ReadAll
objTextFile.Close
arrComputers = Split(strText, vbCrLf)
sQuery = "SELECT * FROM alerts where error_code='29050'AND TIME>='1/1/2006'AND TIME<'2/1/2006'"
for each strserver in arrComputers
Set oADO = CreateObject("ADODB.Connection")
connection = "uid=sa;pwd=*****;driver={SQL Server};server=" & strServer & ";database=pharos"
oADO.Open (connection)
Set oRS = oADO.Execute(squery)
If Not oRS.EOF Then GetResults = oRS.GetString(2,,",",vbCRLF,"")
'oRS.Close : Set oRS = Nothing
oADO.Close : Set oADO = Nothing
If Not IsEmpty(GetResults) Then
Call WriteResults(strServer & ".txt", GetResults)
Else
Call WriteResults(strServer & ".txt", "No records returned." & strComputer)
End If
'would like to add an insert query here that will insert the results of the above recordset into a different table on a different server
Set oADO = CreateObject("ADODB.Connection")
connection = "uid=sa;pwd=*****;driver={SQL Server};server=" & strServer & ";database=OTHERDB"
oADO.Open (connection)
Set oRS = oADO.Execute
oRS.MoveFirst
Do While Not oRS.EOF
sQuery = "INSERT INTO alerts (FIELD1, FIELD2, FIELD3) VALUES (" & oRS.Fields.Item("FIELD1") & ", " & oRS.Fields.Item("FIELD2") & ", " & oRS.Fields.Item("FIELD3") & ")"
oADO.Execute(sQuery)
oRS.MoveNext
Loop
next
Sub WriteResults(filename,results)
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(filename, True) '''overwrite existing
oFile.Write results
oFile.Close : Set oFile = Nothing
Set oFSO = Nothing
End Sub
WARNING: This is completely untested and will not work as written. It is just to point you in the right direction. At the least you will need to put in tje correct db name, db password, db user, and the correct field names.
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: Insert a recordset object into a table - 2/10/2006 5:39:29 AM
|
|
|
|
| |
ebgreen
Posts: 5246
Score: 31
Joined: 7/12/2005
Status: offline
|
That is the other solution I was thinking of...to do it you would get your recordset from db1 then run the same query against db2 to generate a second recordset. Then you go through the first recordset adding each record to the second recordset. At the end UPDATE the second RS back to the second DB.
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: Insert a recordset object into a table - 2/14/2006 3:15:52 AM
|
|
|
|
| |
skione
Posts: 40
Score: 0
Joined: 10/1/2005
Status: offline
|
on error resume next
Dim sQuery, sResults
Dim oADO, oRS
Const ForReading = 1
strCurrentDir = Left(WScript.ScriptFullName,InstrRev(WScript.ScriptFullName,"\"))
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("puterlist.file", ForReading)
strText = objTextFile.ReadAll
objTextFile.Close
arrComputers = Split(strText, vbCrLf)
strServer = "bplpcsb21ps1"
sQuery = "SELECT * FROM alerts where error_code='29050'AND TIME>='1/1/2006'AND TIME<'2/1/2006'"
for each strserver in arrComputers
Set oADO = CreateObject("ADODB.Connection")
connection = "uid=sa;pwd='''''''''''';driver={SQL Server};server=" & strServer & ";database=pharos"
oADO.Open (connection)
Set oRS = oADO.Execute(squery)
oRS.MoveFirst
Set oADO2 = CreateObject("ADODB.Connection")
connection2 = "uid=sa;pwd='''''''''''';driver={SQL Server};server=bplpcsdb;database=Michael"
oADO2.Open connection2
Do While Not oRS.EOF
set afields = ors.fields
set oRS2 = New ADODB.Recordset
' asquery = "Insert into alerts (alert_id, server_id, severity_id, service_type_id, time, error_code, message, operation, client, username, item, timestamp) VALUES ("
asquery = "Insert into alerts "
qryfields = "("
fval = " VALUES ("
for each fld in afields
qryfields = qryfields & fld.Name & ","
fval = fval & "oRS.fields.item(" & fld.value & "),"
next
flen = len(qryfields)
vlen = len(fval)
qryfields = left(qryfields, flen-1) & ")"
fval = left(fval, vlen-1) & ")"
asquery = asquery & qryfields & fval
Call WriteResults(".\results.sql",asquery)
set oRS2 = oADO2.Execute(asquery)
oRS.MoveNext
msgBox "pause"
Loop
oado2.close : set oado2 = Nothing
oRS.Close : Set oRS = Nothing
oADO.Close : Set oADO = Nothing
next
msgbox "done"
Sub WriteResults(filename,results)
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(filename, True) '''overwrite existing
oFile.Write results
oFile.Close : Set oFile = Nothing
Set oFSO = Nothing
End Sub
I am still having difficulty it seems the query failes when it gets to the date fields.
|
|
| |
|
|
|
| |
|
|
 |
|
 |
|
|
Printable Version
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread