Hi,
I'm using access tables to store my data. to retrive data I use jet odbc engine in c++.
I want to insert a large amuont of records (about 20 mega records) to my database in the fasts way i can. if i use sql syntax (insert into table ()....) it's takes for ages (about 500 records per second).
if i'm writing a csv file and then use import (via access) it's much faster but still takes a long time, i can live with that only i dont know how to use the access import tool from c++.
does any know any tool that insert records in an optimize way, or how to use the access import tool in c++? can i use the jet engine for it?
thank ishay

you should use a transaction.

begin;

insert into {table} ({fields}) values ({values})
insert into {table} ({fields}) values ({values})
insert into {table} ({fields}) values ({values})
insert into {table} ({fields}) values ({values})
insert into {table} ({fields}) values ({values})
...
...
...
...

commit;

That way, the db will commit and recalculate the indexes and so on only on the commit statement, and not on each insert.
That can already help, but your DB need to support it.

You could try to use a binded query too.
Look on your db reference for more infos.

Originally Posted by tripy

you should use a transaction.

That way, the db will commit and recalculate the indexes and so on only on the commit statement, and not on each insert.
That can already help, but your DB need to support it.

You could try to use a binded query too.
Look on your db reference for more infos.

Also, if you don't (explicitly) use transactions, the database will use them implicitly. But, it'll wrap each SQL command in its own transaction. This is a great deal of work, and doing it once per session, instead of once per record, should make a tremendous difference.

What do you mean when you say "20 mega" records? 20 million?