Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Insert / Update DB

Insert new employees and update existing employees using the Insert/Update step.

Workshop - Insert / Update DB

Synchronize a file feed to the EMPLOYEES table. Some rows already exist. Others are new.

This pattern is called upsert: update if found, insert if not.

What you’ll do

  • Read a mixed employee feed with Text file input

  • Upsert into EMPLOYEES with Insert/Update

  • Validate inserted and updated rows with SQL

Prerequisites

Estimated time: 20 minutes

Workshop files

Create a file named employees_insert_update.txt. Save it in the same folder as your transformation.

This workshop assumes a comma-delimited file with no header row:

1188,Firrelli,Julianne,x2174,jfirrelli@classicmodelcars.com,2,1143,Sales Manager
1619,King,Tom,x6324,tking@classicmodelcars.com,6,1088,Sales Rep
1810,Lundberg,Anna,x910,alundberg@classicmodelcars.com,2,1143,Sales Rep
1811,Schulz,Chris,x951,cschulz@classicmodelcars.com,2,1143,Sales Rep
Insert / Update

Create a new transformation

Use any of these options to open a new transformation tab:

  • Select File > New > Transformation

  • Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

Text file input

Read the incoming mixed feed from employees_insert_update.txt.

  1. Start Spoon.

  1. Drag Text file input onto the canvas.

  2. Open the step properties.

  3. Configure the file path:

    • File: ${Internal.Transformation.Filename.Directory}/employees_insert_update.txt

Text File input - File

If ${Internal.Transformation.Filename.Directory} is empty, save the transformation first.

  1. Select Content. Use the same delimiter settings as the screenshot.

Text file input - Content

  1. Select Get Fields.

  2. On Fields, confirm you have these stream fields:

    • EMPLOYEE_NUMBER

    • LASTNAME

    • FIRSTNAME

    • EXTENSION

    • EMAIL

    • OFFICECODE

    • REPORTSTO

    • JOBTITLE

Text File input - Fields

  1. Optional: select Preview. Confirm you get 4 rows.

  2. Select OK.

Checkpoint: Preview shows 4 employee rows.

Insert / Update

Upsert into the EMPLOYEES table.

  1. Drag Insert / Update onto the canvas.

  2. Create a hop from Text file input to Insert / Update.

  3. Open the step properties.

  4. Select your database Connection.

  5. Set Target table to EMPLOYEES.

Insert / update options

Key lookup

Map the table key EMPLOYEENUMBER to the stream field EMPLOYEE_NUMBER.

Update fields

Select Get update fields. Then confirm mappings are correct.

Do not add EMPLOYEENUMBER as an update field.

Do not enable Update the keys.

  1. Select OK.

Run and validate

Run the transformation. Then validate the results in the database.

  1. Select Run in the canvas toolbar.

  2. In Execution Results, open Step Metrics.

Step metrics

Expect a mix of inserts and updates, depending on your starting data. You should see activity in the Insert/Update step metrics.

  1. Verify the four employees exist:

Insert / Update Employees

Checkpoint: All four EMPLOYEENUMBER values exist in EMPLOYEES.

Troubleshooting

All rows updated (no inserts) Those employee numbers already exist in EMPLOYEES.

All rows inserted (no updates) Your EMPLOYEENUMBER values were not found, or key mapping is wrong.

Duplicate key errors You likely used Table Output instead of Insert/Update, or you mapped keys incorrectly.

Updates affect too many rows Ensure EMPLOYEENUMBER is unique in your table. Fix duplicates before you upsert.

PreviousUpdate DB table
NextDelete DB table

Last updated

Was this helpful?