Inserting Related MySQL Data with AUTO_INCREMENT

Question

I'm looking at a database that has 3 tables into which I have to insert data:

resource
  id (AUTO_INCREMENT)
  name

resource_item
  id (AUTO_INCREMENT)
  name
  resource_id (FK ref resource.id)

resource_item_business_function
  id (AUTO_INCREMENT)
  business_function_name
  resource_item_id

What I'm struggling with is the fact that this must be scripted. I'm only inserting 1 resource record so I can script the insert into the resource table easily enough. I have ~20 resource_item records to insert for that resource and I can even do that easily enough using the LAST_INSERT_ID() function. The question is...how do I insert into resource_item_business_function?

I have no idea how to insert the proper resource_item_id into each resource_item_business_function record. Any thoughts would be much appreciated.

Answer 1

You would need to use LAST_INSERT_ID() after each insert into resource_item. So your final script could look something like this:

SET AUTOCOMMIT=0;
SET @RESOURCE_ID=0;

INSERT INTO resource ( NULL, "Some Name");
SELECT LAST_INSERT_ID() INTO @RESOURCE_ID;

INSERT INTO resource_item ( NULL, "Some Name", RESOURCE_ID );
INSERT INTO resource_item_business_function ( NULL, "Some Name", LAST_INSERT_ID() );
...etc...
INSERT INTO resource_item ( NULL, "Some Name", RESOURCE_ID );
INSERT INTO resource_item_business_function ( NULL, "Some Name", LAST_INSERT_ID() );

COMMIT;