How many inserts can be done within one transaction in SQLite?

Question

I'm trying to insert a large amount of records (in the millions) into an SQLite database. The data is read in from a file stream (C++). If I begin a single transaction, perform all of the inserts and then commit the transaction, I only get a very small percentage of records actually inserted in my database.

The ones that are inserted seem random -- I can't really see any pattern for which ones do get inserted and which ones get left out. However, if I commit and then begin the transaction again after something like 2000 inserts, I don't have this problem and all of the records are inserted, even though the process is much slower. So...

Is there a strict limit to how many inserts can be done within one transaction? Is there a way to change this limit?

Answer 1

I am able to insert 10 millions rows in a single transaction without a problem.

My guesses:

• you are inserting too much rows in a single statement and hitting Maximum Depth Of An Expression Tree limit. This can be switched off completely.
• you are using overly long INSERTs without binding values and hitting Maximum Length Of An SQL Statement limit. In this case, use parameter binding.
• it may be a bug in the library (are you checking every API return for error codes?)

Check Limits In SQLite. It may help if you show how you prepare and execute INSERTs in the code.