Menu
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?
606
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
SQLite doesn't have any special way to bulk insert data. To get optimal performance when inserting or updating data, ensure that you do the following: Use a transaction. Reuse the same parameterized command.
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).
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
