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:
- Do the insertion through entity framework.
- 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.
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 likeinsert 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 GUID
s instead of int
s, 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?