Converting table columns to key value pairs

Question

I am writing a PL/SQL procedure that loads some data from Schema A into Schema B. They are both very different schemas and I can't change the structure of Schema B.

Columns in various tables in Schema A (joined together in a view) need to be inserted into Schema B as key=>value pairs in 2 columns in a table, each on a separate row. For example, an employee's first name might be present as employee.firstname in Schema A, but would need to be entered in Schema B as:

id=>1, key=>'A123', value=>'Smith'

There are almost 100 keys, with the potential for more to be added in future. This means I don't really want to hardcode any of these keys.

Sample code:

create table schema_a_employees (
    emp_id number(8,0),
    firstname varchar2(50),
    surname varchar2(50)
);
insert into schema_a_employees values ( 1, 'James', 'Smith' );
insert into schema_a_employees values ( 2, 'Fred', 'Jones' );

create table schema_b_values (
    emp_id number(8,0),
    the_key varchar2(5),
    the_value varchar2(200)
);

I thought an elegant solution would most likely involve a lookup table to determine what value to insert for each key, and doesn't involve effectively hardcoding dozens of similar statements like....

insert into schema_b_values ( 1, 'A123', v_firstname );
insert into schema_b_values ( 1, 'B123', v_surname );

What I'd like to be able to do is have a local lookup table in Schema A that lists all the keys from Schema B, along with a column that gives the name of the column in the table in Schema A that should be used to populate, e.g. key "A123" in Schema B should be populated with the value of the column "firstname" in Schema A, e.g.

create table schema_a_lookup (
    the_key varchar2(5),
    the_local_field_name varchar2(50)
);
insert into schema_a_lookup values ( 'A123', 'firstname' );
insert into schema_a_lookup values ( 'B123', 'surname' );

But I'm not sure how I could dynamically use values from the lookup table to tell Oracle which columns to use.

So my question is, is there an elegant solution to populate schema_b_values table with the data from schema_a_employees without hardcoding for every possible key (i.e. A123, B123, etc)?

Cheers.

Answer 1

Hi TomD1,

I sincerely hope that your Schema B is not the dreaded key-value pair design. While some dynamic attribute-value table may be useful in some situation, you will find that all but the most basic queries are nearly impossible to write in the EAV design (even a simple query like "find all employees that are named John Smith" is hard to write -- and impossible to tune).

Anyway, in your case you want to write a dynamic query that will look like this:

SQL> INSERT ALL
  2     INTO schema_b_values VALUES (emp_id, 'A123', firstname)
  3     INTO schema_b_values VALUES (emp_id, 'B123', surname)
  4     SELECT emp_id, firstname, surname
  5       FROM schema_a_employees;

4 rows inserted

You can use the following query to generate the statement:

SQL> SELECT 'INSERT ALL ' sql_lines FROM dual
  2  UNION ALL
  3  SELECT 'INTO schema_b_values VALUES (emp_id, '''
  4            || dbms_assert.simple_sql_name(the_key)
  5            || ''', '
  6            || dbms_assert.simple_sql_name(the_local_field_name)
  7            ||')'
  8    FROM schema_a_lookup
  9  UNION ALL
 10  SELECT 'SELECT * FROM schema_a_employees' FROM dual;

SQL_LINES
--------------------------------------------------------------------------------
INSERT ALL
INTO schema_b_values VALUES (emp_id, 'A123', firstname)
INTO schema_b_values VALUES (emp_id, 'B123', surname)
SELECT * FROM schema_a_employees

You can then use EXECUTE IMMEDIATE or DBMS_SQL to execute that statement.

Answer 2

I like INSERT ALL as an approach because it offers an encapsulated transaction: either all rows are inserted or none are. My experience of data migration is that it tends to be a highly iterative process, so anything which assists in clean up and regression is a distinct boon.

SQL> declare
  2      l_src_name varchar2(30) := 'SCHEMA_A_EMPLOYEES';
  3      l_tgt_name varchar2(30) := 'SCHEMA_B_VALUES';
  4      stmt varchar2(32767);
  5  begin
  6      for pk_rec in ( select cc.table_name, cc.column_name
  7                        from user_cons_columns cc
  8                             , user_constraints c
  9                        where c.table_name = l_src_name
 10                        and c.constraint_type = 'P'
 11                        and cc.table_name = l_src_name )
 12      loop
 13          stmt := 'insert all';
 14          for col_rec in ( select * from schema_a_lookup )
 15          loop
 16              stmt := stmt||' into '||l_tgt_name||' values ('
 17                   ||pk_rec.column_name
 18                   ||', '''||col_rec.the_key||''','
 19                   ||col_rec.the_local_field_name
 20                   ||')';
 21          end loop;
 22          stmt := stmt||' select * from '||l_src_name;
 23      end loop;
 24      execute immediate stmt;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL> 

How many rows?

SQL> select * from schema_b_values;

    EMP_ID THE_K THE_VALUE
---------- ----- ---------------
         1 A123  James
         2 A123  Fred
         1 B123  Smith
         2 B123  Jones

SQL>

I have wrapped the queries in PL/SQL because it points the way towards further automation. You could add a table to hold the SOURCE and TARGET table names. Obviously there's scope for some fun if the source table has a composite primary key.