Execute SQL file in custom database command

Feature requests
1

Hi,

Currently, it is not possible to execute and SQL-file in the database command (e.g. een script with a bunch of update statements).
Is it possible to add this feature to easyMorph ?

thanks !
Nikolaas

2

Duly noted. BTW, we’re going to have a dedicated “Update database” action in the next release. So you may not need to have SQL files with update statements.

3

Hi Dmitry,

I have notice the new feature on the roadmap. How will this feature work exactly ?

Thanks

4

@andrew.rybka, can you please describe here how the “Update database table” action is going to work, and post a screenshot of the action’s properties.

5

Hi Nikolaas,

In the “Update database table” action you will have to :

  • Specify Db column = EasyMorph column or\and Db column = value pairs which should be matched (similar to the “Selected matching DB rows” and “Delete matching DB rows” actions)
  • Select Db table columns that should be updated and the corresponding EasyMorph columns.

The action will use the specified pairs to match DB table and EasyMorph table rows with each other and then update selected DB columns using values from matched EasyMorph table rows.

Here is a screenshot of the action’s properties. This action will match city and company columns from a DB table to the corresponding columns in an EasyMorph table. The action will only affect DB rows whose state column values are equal to CA. And it will update permalink and numEmps DB columns using values from matching rows and corresponding columns from an EasyMorph table.

image
image.png361×689 13.9 KB

6

Hi Andrew,

Thanks. I am looking forward to the release !

Will it be possible to do an upsert with this action (so entirely new records create a new record and records to be updated are updated)?

Use case: we are building a data warehouse. We have an initial load from our source system. Besides that, we have a system that does CDC (change data capture) w.r.t. the initial load. If we could easily process all inserts, updates, deletes in the right order i.e. sorted on timestamp in the CDC-table, with one or two EasyMorph actions, this would greatly simplify an incremental load process.

Any ideas about how we could use this new update action for this case ?

Thanks,
Nikolaas

7

Nikolaas, upsert will not be implemented in the next release because it can’t be implemented with a common workflow for all the supported SQL dialects. But it’s possible that we will implement upsert in one of the following releases.

I can’t tell for your actual case without additional details, but in the general case you will be able to implement upsert with the following workflow:

  • Use the “Select matching DB rows action” to import all the rows which are present in both EasyMorph dataset and target DB table.
  • Use the “Update DB table” action to update those rows
  • Use the “Keep/remove matching” action to create a dataset with rows which should be inserted
  • Use the “Export to DB” action to insert those rows to the target DB table.