0

I am working on a migration project and the data needs to be migrated from system A to system B. The id's in system A and system B will be different. So maintaining a table with source and target column.

As part of the migration, For each record from source A, I need to go to the mapping table to find the corresponding target value. I am just trying avoid querying the mapping table for each query. Is there anyway to store the mapping table as a key value map in stored proc and query the map as opposed to going to the table directly.

I was searching for collections in mysql Stored proc, but dont find anything.

Database A:-

Table : employee


    id    |  name  |       email     | Phone       | Mobile     |  isManager | isEmployee | location_name | secondEmail       | secondName|   companyId | 
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    10021 |  First1 | first@test.com | 123-456-789 | 123-456-787 | 1      |     0   |    Atlanta      |  second@gmail.com | DadName | 34560 |
    ----------------------------------------------------------------------------------------------------------------------------------------

Database B:-

Table : employee

---------------------------------------------
id    |  name  |       email     | location |
---------------------------------------------
1     |  First1 | first@test.com | 1
---------------------------------------------
2     |  First 2| first@test.com | 1
---------------------------------------------

Table : employee_location

 --------------------
    id    |  loc_name  |  
    --------------------
    1     |  Atlanta   | 
    --------------------

Table : employee_second_contact

-------------------------------------------
id    |  SecondEmail     |   SecondNme    |
-------------------------------------------
1     | second@gmail.com | DadName        |
-------------------------------------------

Table : emp_mapping

----------------------------------
source_id | target_id|  type     |
----------------------------------
10021     |  1       |     EMP   |     
----------------------------------
24560     |  1       |    PRJ    |
----------------------------------

Requirement:

  1. Read emp email from source system and check in the target employee table with that email. If email already exist, get id for that email and use that emp id. If emp email is not present, insert into target emp table with that email and get the newly generated id and insert that into emp_mapping table so that other migration can use this mapping table.

SET created_employee_id = LAST_INSERT_ID();

  1. If secondEmail or secondName present in the source employee table, insert that into emp_second_contact table and the id here should be the id from point one.

  2. Also, in the source table, the location will be as a full name It should be inserted as a ID in the target employee table. The id can be retrieved from emp_location static table from the target DB. If the location is not available in the target location static table, insert the location and use that id.

  3. if phone is present insert into emp_phone table.

  4. insert into role table with type based on isEmployee and isManager.

    if (isManager = 1 AND isEmployee = 1) role = DR;

    else if if (isManager = 0 AND isEmployee = 1) role = EMP;

Stored Procedure for Migration

DELIMITER //
CREATE PROCEDURE LoadEmployees()
BEGIN
 DECLARE db1_empl_id INT;
 DECLARE db1_empl_name VARCHAR(20);
 DECLARE db1_empl_email VARCHAR(40);

 DECLARE db2_empl_id INT;

 DECLARE cur CURSOR FOR SELECT id, name, email FROM db1.employees;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_finished = 1;

 
 open cur;
    cur_loop: LOOP
       FETCH cur INTO db1_empl_id, db1_empl_name, db1_empl_email;
                 
       IF cursor_finished = 1 THEN 
          LEAVE cur_loop;
       END IF;
       -- cursor loop statements
       
       -- I would like to move this to a map instead of querying for each record
       select target_id into db2_empl_id from db2.emp_mapping where source_id = db1_empl_id;
       
       insert into db2.employee(id,name,email) values(db2_empl_id, db1_empl_name, db1_empl_email);
       
       SET cursor_finished = 0; -- hence reset this value for cursor.
         
    end LOOP;
 close cur;
END //

DELIMITER ;

Other requirement:

if employee phone is not null, insert into a different table.

IF (employees_Phone IS NOT NULL AND employees_Phone != '' and employees_Phone != employees_MobilePhone) THEN
      insert into db2.user_phone(user_id,phone_nbr,phone_type_cd) values(db2_user_id,employees_Phone,'M');
   END IF; 

   IF (employees_Phone IS NOT NULL AND employees_Phone != '' and employees_Phone != employees_MobilePhone) THEN
      insert into db2.user_phone(user_id,phone_nbr,phone_type_cd) values(db2_user_id,employees_Phone,'P');
   END IF; 

IF (employees_type = 1) THEN
      SET user_role_code = 'MGR';
   ELSE 
      SET user_role_code = 'DR';
   END IF;
   
   -- user_role_mapping pending
   INSERT INTO user_role_mapping(user_id,role_cd) values(user_id,whskr_user_role_code);

There is a type field in the mapping. So, there are multiple fields to bereplaced with the target value from the mapping table based on the type.

Thanks

4
  • Please provide some more visual examples like the table schema for system A and B, the code in the procedure and how it pertains to your question, and some example relevant queries as well. – J.D. Mar 13 at 23:41
  • @J.D. Updated the post. – user1578872 Mar 14 at 0:09
  • So your goal right now is simply to copy all the records from db1.employees to db2.employees with the correct new mapping values of target_id? – J.D. Mar 14 at 2:09
  • Yes, there are other transformation logic as well for few other fields. – user1578872 Mar 14 at 2:10
0

Cursors are typically horribly inefficient, and very rarely used (only when there's not an alternative solution). Using relational logic will be a lot more efficient than a cursor whenever possible, which in this case definitely seems possible. I think all you're looking for is a regular INSERT INTO query using an INNER JOIN between db1.employees and db2.emp_mapping like this:

INSERT INTO db2.employees (id, name, email)
SELECT m.target_id, e.name, e.email
FROM db1.employees e
INNER JOIN db2.emp_mapping m
    ON e.id = m.source_id
WHERE m.type = `EMP`

The beauty of this is this is just a single execution, and single join to your mappings table, that copies all of the employees from db1 in one go. Way more efficient than a cursor and multiple executions of the join. (Whatever other transformation logic you have likely can be included in this one query as well, as needed.)

5
  • I just made my requirement simple. There are many other transformations like if it is 1 or 0 in the source, the target will be Y or N and based on other source values, the target will be setting some other value. – user1578872 Mar 14 at 3:46
  • @user1578872 All sounds reasonable and easily addable to the query I provided. If you provided the transformations logic I can update the query or feel free to take the query and do so yourself. Either way, definitely much more efficient to do relationally either in one or multiple queries that will handle all the records in one batch, as opposed to a cursor doing multiple batches. – J.D. Mar 14 at 4:04
  • I have updated few more details to the requirement. – user1578872 Mar 14 at 5:06
  • 1
    @user1578872 Yes so you would just need a second INSERT INTO statement like the one I did above for employees but for your phone number logic, should be rather simple. I'm not sure which tables the fields in your phone number logic are coming from (same for your user_role_mapping logic), or why you INSERT the same phone number into db2.user_phone twice (once as M then as P), but if you can clarify those things then I can provide the additional INSERT statements. – J.D. Mar 14 at 14:18
  • 1
    updated the post with the requirement. – user1578872 Mar 14 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.