Select Keys from multiple Columns, join with multiple tables And insert data?

I don´t care if it´s done in plain SQL, Functions or Procedures. Although, I would be interesed to know the solution to both. I am migrating data between two databases.

I have:

table EmployeeData: {refColumn1, refColumn2,refColumn3,entryDate, closingDate, age, education,name }
   
table Employees: {refColumn, age, name, nation} 
table Manager: {refColumn, age, adress, store, income}

refColumnX in EmployeeData is either referencing to the refColumn of Employee or Manager. It´s possible that refColumn1 is referencing to an Employee or refColumn1 could reference to a Manager too,but only one (it depends on in what order the data is added). The refColumns in Data can be completely empty or all of them filled.

I want now to move the data from those tables to:

 table Person: {PK,refColumn, income, store, nation}; 
 table Client: {PK,refColumn, age, name, education}; 

The refColumns in the new Tables shall Link to the Employee/Manager tables and the refColumnsX in EmployeeData shall be updated with the PKs of the new Tables. The data fields are in all tables different.

The primary keys of the new Tables shall be created by an existing Function.

Um yeah, what´s a (the best) way to do this? I have tried to do it with a single query, but I somehow can´t connect it properly. Using procedures might be easier.

My idea was to union the refColumns in data to get a List, then build over this a Join with all the otherTables, but I don´t knew how to exactly do this.

Something like this:

Insert into newTable1 (othertable1.data1, ....)
   (Select * from data, newTable1,..newTable_n
   Where otherTable1.refColumn IN
   (select refColumn1 from data union all .... select refColumn_n from data) refs
   Or otherTable2.refColumn in refs .....
   OR otherTable_n.refColumn IN refs);

...And then I would have to use this query for newTable2 to newTable_n.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum