CODEWITHSUNDEEP
sqliteinsertrows
Gehrig
Gehrig

Reputation: 73

SQLITE Insert Multiple Rows Using Select as Value

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?

Upvotes: 5

Views: 3513

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 50970

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).

Upvotes: 6

Related Questions