Bulk Insert from Recordset to Table

[carlmack]

Does anybody know a way to bulk insert from a recordset to a table (assuming the same structure). I can loop round the recordset using an INSERT INTO SQL execute but I have many records and it is slow.

Is this possible using ADO or DAO ?

Carl

 

[FoFa]

Using ADO, and the rs.ADDNEW you can use rs.UPDATEBATCH instead of rs.UPDATE and it does batch the transactions to the DB, and it does give a slight speed increase. From non-traced, gut feel I would say about 10% MAYBE increase in speed.

 

[modest]

Create a temporary table that the recordset is based from, then just append the temp table to the real table.

 

[carlmack]

Many thanks for the replies.

FoFa, Thanks alot I will give that a go.

Modest, Do you mean use a table instead of a recordset ? Unfortunately I have quite a bit of manipulation to do on the recordset so wouldn't this be slower ?

Regards

Carl