Insert recordset into the table

[vladk]

Hi,

I am inserting my recorset into a table by executing INSERT INTO .... VALUES for each record in the recordset and by moving from one record to another. This is quite slow process.

Is it possible to insert the entire recordset into the table with a single shot?

Thank you!

vladk

 

[lespaul]

I use this all the time:

INSERT INTO SomeTable (SELECT * FROM SomeOtherTable)

this would require that the structure of each table is the same. If you have a query that lists all the fields in SomeTable, then you can use the same format:

INSERT INTO SomeTable (SELECT Field1, Field2, Field3, Field4 FROM SomeOtherTable S INNER JOIN AnotherTable A ON A.ID = S.ID)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[vladk]

Leslie,

Thank you very much for your response. I will try it today or tomorrow. This is important thing for me.

Thank you

vladk

 

[vladk]

Actually, Leslie, I need to insert ADODB.Recordset records, not the table, into some table.

 

[lespaul]

are you getting your recordset via a query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[vladk]

Leslie,

Yes

vladk

 

[lespaul]

can you post the code and the query you're trying to use?

 

[vladk]

Leslie,

I apologize for the late reply. I was thinking, the thread was over. This is the snippet of the code:

Private mobjCmnd As ADODB.Command

Private Sub ExportQrsToTbls()

.....
Dim objRcrdst As ADODB.Recordset
....

mobjCmnd.CommandText = "SELECT * FROM " & strTable 'Just some select
Set objRcrdst = mobjCmnd.Execute

If objRcrdst.RecordCount > 0 Then
objRcrdst.MoveFirst
While Not objRcrdst.BOF And Not objRcrdst.EOF
ExecuteSQLDDL "INSERT INTO " & strTable & " Values (" & _
"'" & objRcrdst.Fields("RiskSt").Value & "'" & "," & _
"'" & objRcrdst.Fields("Prog").Value & "'" & "," & _
"'" & objRcrdst.Fields("Coverage").Value & "'" & "," & _
"'" & objRcrdst.Fields(strExposureByField).Value & "'" & "," & _
"#" & objRcrdst.Fields("QuarterDate").Value & "#" & "," & _
objRcrdst.Fields("EarnedLocationYears").Value & ")"

objRcrdst.MoveNext
Wend
End If

End Sub

Public Sub ExecuteSQLDDL(ByVal pstrSQLString As String)

Dim objDB As DAO.Database
Dim objQD As DAO.QueryDef

Set objDB = DBEngine.Workspaces(0).Databases(0)
Set objQD = objDB.CreateQueryDef("")

objQD.SQL = pstrSQLString

objQD.Execute

objDB.Close

End Sub

Thank you

vladk