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?

Larry Lustig · Accepted Answer · 2013-12-21 00:40:06Z

6

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

edited Dec 21, 2013 at 0:40

answered Dec 19, 2013 at 21:12

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLITE Insert Multiple Rows Using Select as Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related