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();

Answer 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?

Answer 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