Problem with loading target tables

[yaminids]

Hello there,

I have one source table(A) with details of employees. It contains Dept.Name and Dept.Number columns along with some other columns. A person can work in different departments. In the target database, I have two tables namely employee table(B) and employee_account table(C). If a person(Sam) works in departments 5 and 6, I want to load 'emp_name' column in table B with 'Sam' and also I want to have two records of 'Sam' in table C with dept_number 5 and 6. Can anyone explain me how I can accomplish this please.

Thanx in advance.
-Yamini

[chucksmith]

There are many ways, but I would write the rows going to table B to a hash file with employee as key, and write the rows going to tables C to a sequential file.

Since your hash file is keyed on employee, you end up with one row for Sam in the hash file. The sequential file will have two rows for Sam.

If you need to bulk load table B, then read the hash file and write to a sequential file. You still have one row for Sam.

Now, you can bulk load or insert rows from the respecitve files to your tables.

Wrap it in a sequencer, and you are done.

[vmcburney]

What you want to output is one duplicate employee stream to table C and a deduplicated stream to table B. You can perform the deduplication or duplication in the SQL select statement.

For example if you already have duplicate employees in your source employee table, one record for each department an employee is in, then table C is loaded by a straight select statement while table B is loaded by a GROUP BY or DISTINCT select statement that removes duplicates (leaving out the department information).

If your source is relational where an employee table links to an employee_department table then you join the two together to create duplicate employee records for table B.