Using a cursor inside a stored procedure to populate normalized tables

[Siddharth Kumar]

Hi

I am trying to populate volume data using a stored procedure.

I have a parent table which has a primary key as auto increment.The parent has about 4 normalized child tables with a one to many relation between them. The parent table primary key is a foreign key for this table

Because i need to insert the parent record, get the primary key and then use that as a foreign key for this child records the approach that first came to mind is

Create a cursor which loops around the staging table

Inserts 1 record into the parent table
Select select LAST_INSERT_ID() into a variable
Then insert into all the child tables using the parent table Id for foreign key.

This is repeated in a loop.

I am though having some serious performance issues. I am working with a sample data set for development which is about 10k parent records and maybe as many child records/table for now.

Any advice? I have tried similar approaches in other databases and have never encountered this performance issues.

Any alternative approach would be appreciated.

Regards

Sid