Update DB

Workshop - Update DB

The Update step first looks up a row in a table using one or more lookup keys. If it can be found and the fields to update are all the same, no action is taken, otherwise the record is updated.

In this guided Lab, you will:

• Update the Employees Table.

Update Employees

1002, Murphy, Diana,x5800,[e-mail],1,1000,CEO

1102, Bondur, Gerard,x5408,[e-mail],4,1056,Regional Sales Manager (EMEA)

---

Create a new Transformation

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

• By clicking File > New > Transformation

• By using the CTRL-N hot key

1. Text File input

Text file input

The Text File Input step is used to read data from a variety of different text-file types. The most commonly used formats include Comma Separated Values (CSV files) generated by spreadsheets and fixed width flat files.

The Text File Input step provides you with the ability to specify a list of files to read, or a list of directories with wild cards in the form of regular expressions. In addition, you can accept filenames from a previous step making filename handling more even more generic.

1. Drag the Text File Input step onto the canvas.

2. Open the Text File properties dialog box.

3. Ensure the following details are configured, as outlined below:

Set path to employess.txt

Because the sample file is located in the same directory where the transformation resides, a good approach to naming the file in a way that is location independent is to use a system variable to parameterize the directory name where the file is located. In our case, the complete filename is:

${Internal.Transformation.Filename.Directory}/employees_update.txt

1. Click on the ‘Content’ tab and configure the following properties:

Text file input - Content

1. Click on ‘Get Fields’ button.

Click on the ‘Fields’ tab and notice the following properties:

Text File input - Fields

1. Close the step.

2. Update

Update

The Update step first looks up a row in a table using one or more lookup keys. If the row matches the lookups, then it updates the record.

1. Drag the Update step onto the canvas.

2. Open the Update properties dialog box.

3. Ensure the following details are configured, as outlined below:

Update fields

Set the Key to lookup the values

Select EMPLOYEENUMBER from the table to equal EMPOLYEE_NUMBER from the data stream.

You can set any number of constraints to ensure unique values are look up.

Get Update fields

Ensure the Table and Stream fields are correctly mapped.

1. Click Ok.

3. RUN

RUN

Database operations rely on Keys - unique identifier - to maintain referential integrity.

If no matching record is found based on the key fields, the Update step simply skips that row without performing any action. Unlike the "Insert/Update" step, it doesn't create new records when matches aren't found.

The Update step is particularly useful for data maintenance operations, synchronizing systems, or applying changes to existing database records without disturbing their structure or unaffected fields.

1. Click the Run button in the Canvas Toolbar.

2. Click on the Step Metrics tab.

Step metrics

Notice that 2 records were updated with the Update employees step.

1. Check the records in the database.

select * from EMPLOYEES
where EMPLOYEENUMBER in ('1002','1102');

Update Employees