ado.net - 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

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