Insert multiple values with the same foreign key-CodePudding

I have two tables that reference each other:

CREATE TABLE Room
    room_id INTEGER PRIMARY KEY,
    room_name TEXT UNIQUE NOT NULL;

CREATE TABLE Item
    item_id INTEGER PRIMARY KEY,
    room_id INTEGER,
    item_name TEXT,
    FOREIGN KEY (room_id) REFERENCES Room (room_id) ON UPDATE RESTRICT ON DELETE RESTRICT;

Now I want to add a new room and add a few dozen items to go into it.

INSERT INTO Room(room_name) VALUES ('Living Room');

Let's say I don't know how many rooms there are, and I just want to put stuff into the living room. To do that, I need to select the right room_id. For a single item this is not too bad:

INSERT INTO Item(room_id, item_name)
    SELECT room_id, 'Couch' AS item_name FROM Room WHERE room_name = 'Living Room';

But what if I want to insert a bunch of values simultaneously. I tried using last_insert_rowid, but that does not treat the entire INSERT as a single transaction. In other words, the last ID keeps incrementing

INSERT INTO Item (room_id, item_name)
  VALUES
    (last_insert_rowid(), 'Chair'),
    (last_insert_rowid(), 'TV'),
    (last_insert_rowid(), 'Carpet');

I would like to avoid having to use the SELECT on each new row. Is there a way to insert multiple values into Item, while referencing the last known room_id in Room?

Something in the nature of a CROSS JOIN would likely be very useful, but I don't know how to get the constants to behave in that case

The end result I am looking for is for Room to look like this:

room_id | room_name
-------- -----------
      1 | Living Room

And Item like this:

item_id | room_id | item_name
-------- --------- -----------
      1 |       1 | Chair
      2 |       1 | TV
      3 |       1 | Carpet

CodePudding user response：

You can use a CROSS join of the id that you get from the new room to a CTE that returns the items that you want to insert:

WITH cte(item_name) AS (VALUES ('Chair'), ('TV'), ('Carpet'))
INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r CROSS JOIN cte c
WHERE r.room_name = 'Living Room';

See the demo.

If you are using a version of SQLite that does not support CTEs use UNION ALL in a subquery:

INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r 
CROSS JOIN (
  SELECT 'Chair' item_name UNION ALL 
  SELECT 'TV' UNION ALL
  SELECT 'Carpet'
) c
WHERE r.room_name = 'Living Room';

See the demo.