Bulk insertion into SQLite.Net database

Question

I need to perform bulk insetion into a sqlite database in visual studio. Does anybody know how to do this?

You might want to look at [this](http://stackoverflow.com/questions/364017/faster-bulk-inserts-in-sqlite3) answer to a similar question here on SO. — lexu, Jul 15 '09 at 15:51

score 7 · Answer 1 · edited Jun 06 '12 at 02:40

7

With SQLite you can do this by opening a transaction, looping through all your data and executing the relevant SQL INSERT commands, and than commit the transaction. This is the fastest way to perform bulk inserts in SQLite.

edited Jun 06 '12 at 02:40

Andrew Barber

37,547
20
91
118

answered Jul 27 '09 at 09:02

Liron Levi

1,109
6
10

This is the fastest way for any ADO compliant DB driver : http://sqlite.phxsoftware.com/forums/t/134.aspx One note is that the connection needs to be open before you BeginTransaction – basarat Apr 04 '11 at 13:44

score 3 · Answer 2 · answered Aug 12 '16 at 04:02

I'm using the SQLite.Net.Async-PCL nuget package for working with sqlite in my UWP app and I found that the InsertAllAsync method is really fast. It was able to insert almost 33,000 rows into a table in less than a second where it took almost 10 minutes when I used a loop and just called InsertAsync.

I checked the source and it is using a transaction for the InsertAll method, so it's nice that's all taken care of already and you can just pass in your list of items and not have to jump through any hoops.

score 2 · Answer 3 · answered Oct 27 '11 at 03:22

    private void prepareConnectionForBulkInsert(SQLiteConnection cn)
    {

        SQLiteCommand stmt;

        stmt = new SQLiteCommand("PRAGMA synchronous=OFF", cn);
        stmt.ExecuteNonQuery();

        stmt = new SQLiteCommand("PRAGMA count_changes=OFF", cn);
        stmt.ExecuteNonQuery();

        stmt = new SQLiteCommand("PRAGMA journal_mode=MEMORY", cn);
        stmt.ExecuteNonQuery();

        stmt = new SQLiteCommand("PRAGMA temp_store=MEMORY", cn);
        stmt.ExecuteNonQuery();

    }

score 1 · Answer 4 · answered Jul 15 '09 at 15:41

1

You can just write a regular insert statement and loop through your dataset, inserting the relevant data into the query string before running it against the database.

answered Jul 15 '09 at 15:41

Mike Trpcic

23,821
7
71
111

score 1 · Answer 5 · answered Sep 08 '09 at 19:53

1

I wrote a class to help facilitate bulk inserts in SQLite. Hopefully it's helpful:

http://procbits.com/2009/09/08/sqlite-bulk-insert/

-JP

answered Sep 08 '09 at 19:53

JP Richardson

35,950
34
117
150

score 0 · Answer 6 · answered Sep 08 '09 at 17:30

0

I needed to do the same thing, I found this answer on the Sqlite.Net forum, might be of interest for someone.

http://sqlite.phxsoftware.com/forums/t/134.aspx

answered Sep 08 '09 at 17:30

Emmanuel

1,511
11
12

The page doesn't exist anymore, this is why answers should contain the answer and you can add the link for additional info. – Percy Jun 18 '17 at 13:57

Bulk insertion into SQLite.Net database

6 Answers6