I’m wondering if it is possible to update tables in a database using a transformation. My co-workers rely heavily on Excel and I have them copy data into spreadsheets and use various transformations to insert the data in the database and it works great.
Now, I need them to take care of updating data here and there (like the status of an administration or date something closed) and right now they maintain that locally in a spreadsheet which I then manually change in the database. if I could create a project that could accomplish this, that would be perfect. Still really new to this ETL / database stuff so I’m learning as I go Any suggestions would be great.
Import necessary rows into EasyMorph using either the “Import from database” transformation (using a query) or “Select DB rows” (using a list of IDs). See the Advanced Topics in this tutorial article: http://easymorph.com/learn/load-database.html.
Modify the rows as necessary in EasyMorph (e.g. update the close date).
Delete the rows in the database using either Delete DB Rows (using a query) or Delete matching DB Rows (using a list of IDs). See the Advanced Topics in this article: http://easymorph.com/learn/export-database.html)
Export modified rows into the target table using the “Export to database” transformation.
To make it a bit more fail-proof, you can use two more steps:
Add a new step after step 1 above:
1a. Save the rows into a temporary .dset file using the “Export dataset” action.
Add a new step after step 4 above:
4a. Delete the temporary file using the “File command” action.
Saving the original data into a temporary file ensures that it isn’t lost in case for some reason database rows have been deleted in step 3, but the modified data failed to export in step 4. In case it happened, you can export the saved data from the temporary file back into the database after the cause of failure is fixed.
Any suggestions would be great.