How do I bulk insert with SQLite?

Question

How do I bulk insert with SQLite?

I looked it up and it seems like I do an insert with a select statement. I googled, looked at the examples and they all look like they are copying data from one table to another or is not compatible with SQLite. I want to do something like

"INSERT INTO user_msg_media (recipientId, mediaId, catagory, current_media_date) " +
"VALUES(@mediaId, @catagory, @current_media_date)";
where the value of recipientId is the watcher from each of
"SELECT watcher FROM userwatch WHERE watched=@watched";

I tried the code below and I get the error "SQLite error no such column: watcher"

        command.CommandText =
            "CREATE TABLE if not exists user_msg_media( " +
            "msgId        INTEGER PRIMARY KEY, " +
            "recipientId  INTEGER, " +
            "mediaId      INTEGER, " +
            "catagory     INTEGER, " +
            "current_date DATE);";
        command.ExecuteNonQuery();

        //user media
        command.CommandText =
            "CREATE TABLE if not exists user_watch( " +
            "indx INTEGER PRIMARY KEY, " +
            "watcher INTEGER, " +
            "watched INTEGER);";
        command.ExecuteNonQuery();
        //...

    command.CommandText = "SELECT watcher FROM user_watch WHERE watched=:watched;";
    command.Parameters.Add(":watched", DbType.Int64).Value = 1;
    command.ExecuteNonQuery(); //is ok

    command.CommandText =
        "INSERT INTO user_msg_media (recipientId, mediaId, catagory, current_media_date) " +
        "SELECT watcher, :mediaId, :category, :current_media_date" +
        "FROM user_watch WHERE watched=:watched;";
    command.Parameters.Add(":mediaId", DbType.Int64).Value = 0;
    command.Parameters.Add(":category", DbType.Int64).Value = 0;
    command.Parameters.Add(":current_media_date", DbType.Int64).Value = 0;
    command.Parameters.Add(":watched", DbType.Int64).Value = 1;
    command.ExecuteNonQuery();

Solution 1

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_date
FROM 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 sq

con = 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?

Solution 2

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

Solution 3

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.

Comments

[Alex Martelli]

That suggests one of your column names is spelled wrong - what's the CREATE TABLE statement for table user_msg_media then?

[Alex Martelli]

(i.e., assuming whatever language you're using uses the same syntax for placeholders as pysqlite and you're also passing in the right dict, hash, or whatever)...

[Admin]

CREATE TABLE statement for user_msg_media !?! isnt it looking in userwatch? i'll edit both in

[Admin]

Thank you! it works! Not only for explaining how to do it but also for finding my bug. There was 2, 1) :current_media_date" + "FROM" <-- no space in between which gave the false column error and the other was current_date being wrong. This works great thanks.

[Admin]

haha, 2.5 years to late. However i am using dapper (or selects) and my own orm (for everything else) which kicks the ass out of subsonic and the other crapped called orms

[BrokeMyLegBiking]

I am glad to hear you are having success with Dapper. I just recommended it for inclusion in our production application.