DAO Recordset Insert

[mterveen]

Have a VB6 app that transfers info from dbase to SQLSERVER 7 tables. the app use a dao recordset to join several dbase files and select/group/sum the records. the recordset is then inserted to an sql table using the addnew method. two questions: 1) is there a way to use the "insert into" syntax using the dao recordset to do a bulk insert versus the painfully slow addnew, and 2) if not, is the "insert into tablename (fieldnames) values (values)" syntax faster than addnew? thanks.

 

[SQLSister]

IF you are pulling information from other tables to insert, I would suggest using the insert with the select clause instead of the values clause. This can be used to insert multiple records.

Example
Insert into table1 (col1, col2, col3)
Select sum (col1), sum (Col2), col3 from table2
Group by col3
Where Col4 = 'test'

 

[mterveen]

Unfortunately it is a recordset i have (versus tables) so that is the reason for my question. i need to join the dbase tables/files and have found that the only reliable method is to use the dao provider. so i'm stuck at this point with a dao recordset. the recordset methods include a "getrows" method which will bulk load the recordset into an array. however, i then need to know if there is a way to use the array in an "insert into ... select" statement. appreciate the help.

 

[mterveen]

thot i should update the post. after a LOT of trial and error i got the insert into ... openrowset syntax to work and its very fast. hardest part was the proper setup of the dbase provider syntax. however, as always, once it works the answer looks so simple!!