Get LAST_INSERT_ID() after INSERT and use it in following queries

Question

I'm inserting a row in one table like:

INSERT INTO first (name) VALUES ('something');

and then get the auto-incremented ID of that row like:

SELECT LAST_INSERT_ID();

Then, I want to use this particular ID in a series of INSERTs like:

INSERT INTO second (f,a,b) VALUES (ID, 'something else', 'with something else');
INSERT INTO second (f,a,b) VALUES (ID, 'something else 1', 'with something else 3');
INSERT INTO second (f,a,b) VALUES (ID, 'something else 2', 'with something else 4');

The question is: how do I "assign" that first LAST_INSERT_ID() to some sort of variable, so that I can reuse it in the following queries. I've tried with WITH and AS and all that, but none of that worked.

(basically my programming mind is craving for something like: ID = SELECT LAST_INSERT_ID(); and then go on to execute the next queries... but I cannot figure out how to do it.)

Answer 1

SQLite does not support variables in sql statements.

If you have defined the column ID of the table first as:

ID INTEGER PRIMARY KEY AUTOINCREMENT

then you know that the last inserted value is the max ID of the table, so you can insert the rows to the table second like this:

INSERT INTO second (f,a,b) VALUES ((SELECT MAX(ID) FROM first), 'something else', 'with something else');

if you are sure that there is at least 1 row in first.

Or better:

WITH 
  cte(a, b) AS (
    VALUES ('something else', 'with something else'),
           ('something else 1', 'with something else 3'),
           ('something else 2', 'with something else 4')
  )
INSERT INTO second (f, a, b) 
SELECT f.id, c.a, c.b
FROM (SELECT MAX(id) id FROM first) f
CROSS JOIN cte c
WHERE f.id IS NOT NULL

See the demo.