Mysql – Creating map and use that within the thesql stored proc

MySQLstored-procedures

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

Best Answer

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.)

Related Question