SQLITE Insert Multiple Rows Using Select as Value

Question

I have a sqlite statement that will only insert one row.

INSERT INTO queue (TransKey, CreateDateTime, Transmitted) 
VALUES (
 (SELECT Id from trans WHERE Id != (SELECT TransKey from queue)),
 '2013-12-19T19:47:33',
 0
)

How would I have it insert every row where Id from trans != (SELECT TransKey from queue) in one statement?

Answer 1

INSERT INTO queue (TransKey, CreateDateTime, Transmitted) 
 SELECT Id, '2013-12-19T19:47:33', 0 
 FROM trans WHERE Id != (SELECT TransKey from queue)

There are two different "flavors" of INSERT. The one you're using (VALUES) inserts one or more rows that you "create" in the INSERT statement itself. The other flavor (SELECT) inserts a variable number of rows that are retrieved from one or more other tables in the database.

While it's not immediately obvious, the SELECT version allows you to include expressions and simple constants -- as long as the number of columns lines up with the number of columns you're inserting, the statement will work (in other databases, the types of the values must match the column types as well).