4

We are doing migration from SQL Server 2012 to MySQL 5.6. One of the scenarios that came up is inserting bulk records in parent child table. An example:

create table parent (
parent_id int primary key auto_increment,
parent_name varchar(100) );

create table child (
child_id int primary key auto_increment,
child_name varchar(100) ,
foreign key (parent_id) references parent(parent_id));

Say I have two temp tables parent_temp and child_temp and I want to insert the records into parent and child tables. The problem is that I need to keep track of the auto-generated parent_id column with the parent_temp_id. In SQL Server, we used Output into statement to work around this problem. Since there is no direct method available here, here are some straight forward solutions that I could think of:

  1. Do the insertion through entity framework.
  2. Use a while loop to iterate the parent records and do an insertion in the parent table, get hold of the auto-generated key and insert into child table. And so on.
  3. Add a spare column dummy_col in the parent table to hold the mapping. This will allow bulk inserts for parent table. The insert query looks like

    insert into parent(parent_name,dummy_col ) select parent_temp_name, parent_temp_id from parent_temp

    In this way we will have a 1-1 mapping between the rows of parent and parent_temp tables. The child table query looks like

    insert into child(child_name,parent_id) select child_temp_name, p.parent_id from child_temp ct inner join parent p on p.dummy_col = ct.parent_temp_id

The problem with approaches 1 and 2 is that they are slow for bulk insertions. We could be inserting easily 15k rows at one time. Approach 3 will be problematic if two or more users are simultaneously running the same insertion query, and if their parent_temp_id's match since (we are using int, and they would be always be starting from 1,2,3,4...). If we use GUIDs instead of ints, we can probably avoid this duplicate issue. But we would always need to create extra columns in such tables and make sure that they are not used for some other purpose.

Based on the above scenario, are there any other solutions for MySQL? And which one would you prefer?

Gilles
  • 9,269
  • 4
  • 34
  • 53
trace_70
  • 41
  • 2

0 Answers0