Inserting new rows where a simple primary key has to be generated

For this recipe you will use the Pentaho sample database. If you don't have that database, you'll have to follow the instructions in the introduction of this chapter.

As you will insert records into the office table, it would be good if you explore that table before doing any insert operations. Here you have a sample query:

SELECT OFFICECODE, ADDRESSLINE1, CITY
FROM   OFFICES
ORDER BY OFFICECODE;
+------------+--------------------------+---------------+
| OFFICECODE | ADDRESSLINE1             | CITY          |
+------------+--------------------------+---------------+
| 1          | 100 Market Street        | San Francisco |
| 2          | 1550 Court Place         | Boston        |
| 3          | 523 East 53rd Street     | NYC           |
| 4          | 43 Rue Jouffroy D'abbans | Paris         |
| 5          | 4-1 Kioicho              | Tokyo         |
| 6          | 5-11 Wentworth Avenue    | Sydney        |
| 7          | 25 Old Broad Street      | London        |
+------------+--------------------------+---------------+
7 rows in set (0.00 sec)

In many situations, before inserting data into a table you have to generate the primary key. If the primary key is a simple sequence or the maximum primary key plus one, you can generate it by using a Combination lookup/update step.

In the recipe, for each row in your file, with the Combination lookup/update step, you look for a record in the offices table with the same values for address, city, and country.

Because the offices are new, (there aren't offices in the table with the same combination of address, city, and country values) the lookup fails. As a consequence, the step generates a key value as the maximum OFFICECODE in the table, plus 1. Then, it inserts a row with the generated primary key and the fields you typed in the grid.

Finally, the step adds to the stream the generated primary key value.

As a last task, we used that key to update the other fields coming into the file: POSTALCODE and PHONE.

The Combination lookup/update step is within the Data Warehouse category, because is mainly used for loading junk dimension tables. But as you could see, it can also be used in the particular situation where you have to generate a primary key.

In the recipe you generated the PK as the maximum plus one, but as you can see in the setting window, a database sequence can also be used instead.