Update table in SQL if records exist, else than insert

[Christina_Lee]

Hi there,

I have workflow set up in Easymorph and will export the data to SQL server.

The purpose is that:

• if the data records are already in SQL table, then we need to update the records
• If the data records don’t exist in SQL table, we insert the data

I notice that the Export to Database and Update Database table is separate action, is there any way that we can combine the above conditions in one action? or any other suggestion?

Thank you!

[dgudkov]

Hi Christina,

updating and inserting can't be combined into one operation in EasyMorph. Instead, derive two tables from your main table. In one table, use filters to keep the records that already exist and for them do "Update DB table". In the other table, keep only the records that don't exist, and for them do "Export to database".

See also:

Execute SQL file in custom database command

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…

Snowflake Update/Insert

Hi Luis and welcome to the Community! While there is no “UPSERT” action in EasyMorph, the canonical way of doing upserts is to derive two tables, and then use the “Keep/remove matching” action to keep records that already exist (and need to be updated) in one derived table, and new records (that need to be inserted) in the other derived table. Then use the “Update” action in one derived table, and “Export to database” in the other. Also, to check whether a record ID exists in Snowflake, you ca…

Delta derivation

To verify if the incoming data already exists, use the “Select matching database rows” action with the key field. After that, split your incoming dataset into two parts: new data and changed data. Use the “Export to database” action to insert new data, and the “Update database” action to update data in Redshift.

[Christina_Lee]

Thank you! ya the way you suggested is the way I’m trying right now.

But good to get your confirmation. thanks

[dgudkov]

You’re welcome!