sqlmariadbgenerated

Insert generated key from one table into second table with batch in one request

There are two tables with the dependence one to many on field occupation_id (project about booking hotel rooms). Each occupation entity can have several booked rooms. Is there way to do in a single sql request insertion to the first table (occupation) and several batch insertions to the second table(booked_rooms).

Tables are:

Table: Occupations
occupation_id bigint (autoincrement)
user_id bigint
check_in_date date
check_out_date date
status text

Table: Booked_rooms
booked_room_id bigint (autoincrement)
occupation_id bigint
beds int
class_rate int
Solution

  • Use a transaction:

    BEGIN;
INSERT INTO ...;
SELECT LAST_INSERT_ID();
INSERT INTO ...;
COMMIT;