How do I bulk insert with SQLite? How do I bulk insert with SQLite? sqlite sqlite

How do I bulk insert with SQLite?


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


SQlite doesn't support @variable notation, but (using named-placeholder style as supported by the Python binding of sqlite for clarity) this should work:

INSERT INTO user_msg_media (userId, mediaId, catagory, current_media_date)SELECT watcher, :mediaId, :category, :current_media_dateFROM userwatch WHERE watched=:watched

Edit: SQLite seems to be misdiagnosing what column name is wrong. With column names all fixed, the following Python code works for me (not sure what other language you're using, Python's what handiest to me to interact with sqlite):

import sqlite3 as sqcon = sq.connect(':memory:')cur = con.cursor()cur.execute("CREATE TABLE if not exists user_msg_media( " +            "msgId        INTEGER PRIMARY KEY, " +            "recipientId  INTEGER, " +            "mediaId      INTEGER, " +            "catagory     INTEGER, " +            "current_date DATE)")cur.execute("CREATE TABLE if not exists user_watch( " +            "indx INTEGER PRIMARY KEY, " +            "watcher INTEGER, " +            "watched INTEGER)")cur.execute("INSERT INTO user_watch VALUES (1, 2, 3)")cur.execute("SELECT watcher FROM user_watch WHERE watched=:watched",            dict(watched=3))print cur.fetchall()print cur.execute("INSERT INTO user_msg_media (recipientId, mediaId, catagory, current_date) " +        "SELECT watcher, :mediaId, :category, :current_media_date " +        "FROM user_watch WHERE watched=:watched;",        dict(mediaId=0, category=0, current_media_date=0, watched=3))cur.execute("SELECT * FROM user_msg_media")print cur.fetchall()

But if I reproduce mismatches in your SQL such as current_date vs current_media_date, I can get it to mis-diagnose that the missing column is watcher, even though that column is in fact fine. Want to try putting this corrected code back into your favorite language and see how it behaves?


I would suggest using Dapper ORM from our friends at StackOverflow. If you look at the section on performance, you can't get much faster than Dapper.

Execute a Command multiple times
The same signature also allows you to conveniently and efficiently execute a command multiple times (for example to bulk-load data)

Example usage:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }  ); // 3 rows inserted: "1,1", "2,2" and "3,3"

This works for any parameter that implements IEnumerable for some T.