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
EMPLOYEESwith Insert/UpdateValidate inserted and updated rows with SQL
Prerequisites
A working database connection. See Database Connections.
Basic primary key concepts (
EMPLOYEENUMBER)
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,[email protected],2,1143,Sales Manager
1619,King,Tom,x6324,[email protected],6,1088,Sales Rep
1810,Lundberg,Anna,x910,[email protected],2,1143,Sales Rep
1811,Schulz,Chris,x951,[email protected],2,1143,Sales Rep
Create a new transformation
Use any of these options to open a new transformation tab:
Select File > New > Transformation
Use
Ctrl+N(Windows/Linux) orCmd+N(macOS)
Start Spoon.
Drag Text file input onto the canvas.
Open the step properties.
Configure the file path:
File:
${Internal.Transformation.Filename.Directory}/employees_insert_update.txt
If ${Internal.Transformation.Filename.Directory} is empty, save the transformation first.
Select Content. Use the same delimiter settings as the screenshot.
Select Get Fields.
On Fields, confirm you have these stream fields:
EMPLOYEE_NUMBERLASTNAMEFIRSTNAMEEXTENSIONEMAILOFFICECODEREPORTSTOJOBTITLE
Optional: select Preview. Confirm you get 4 rows.
Select OK.
Checkpoint: Preview shows 4 employee rows.
Drag Insert / Update onto the canvas.
Create a hop from Text file input to Insert / Update.
Open the step properties.
Select your database Connection.
Set Target table to
EMPLOYEES.
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.
Select OK.
Select Run in the canvas toolbar.
In Execution Results, open Step Metrics.
Expect a mix of inserts and updates, depending on your starting data. You should see activity in the Insert/Update step metrics.
Verify the four employees exist:
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.
Last updated
Was this helpful?