Exporting data into a database

Exporting data into a database usually takes two steps:

  1. Create a database table (if necessary).
  2. Export data from EasyMorph into the database table using either direct export (simple but slow) or bulk loading (fast but more complex).

Creating a database table

To create a database table, use the "Database command" action. In this action, pick a database connector, and then provide a new table name. While most database types allow table names containing spaces and letters in mixed upper/lower cases, it is recommended to choose table names that have no spaces and contain either all upper-case letters, or all lower-case.

To facilitate dealing with database data types, regardless of database type, EasyMorph simplifies all data types to just three: Number, Text, and Date. EasyMorph automatically detects data types and picks appropriate database types depending on current database connector. You can adjust auto-detected types, if necessary. It's also possible to specify data types native to the database, instead of simplified ones.

Hint: To create a table in a particular database schema, prepend the table name with the schema name. For instance: myschema.mytable. Note that the schema must already exist.

Exporting into a database table

To export into a database table, use the "Export to database" action. Under the hood, this action employs SQL INSERT to insert batches of 10/100/1000 rows into the target database table. The target table must already exist before using the "Export to database" action. Prior to exporting data, EasyMorph automatically detects data types in the target table. In case of type mismatch for a particular value, it will be exported as NULL or as a default value. Therefore, it's typically a good idea to ensure your data is clean and consistent before exporting. EasyMorph has many actions that can help with this.

Video: Queries and Export to Database in version 3.3 (preview)

Watch a similar video in

Hint: In order to map fields in EasyMorph to fields in an existing database table, just rename EasyMorph fields so that the new names exactly match corresponding field names in the database. You can do the renaming and export in a derived table if you prefer to keep working with unchanged names in EasyMorph.

Advanced topics

Bulk export (SQL Server, Postgres, Snowflake)

Bulk export is recommended for large datasets (>1 million rows), when performance may be critical. The "Bulk export to database table" action performs fast data insertion into a database table using bulk export mechanisms provided by the target database. Using the "Bulk export to database" action is straight forward. It is very similar to the regular "Export to database" action described above.

Bulk export (other database types)

For all the other database types that are not supported by the "Bulk export" action, it's still possible to perform bulk export by combining a few actions. Typically, it works as follows:

  1. Export data from EasyMorph into a temporary text file (e.g. CSV).
  2. (Optional). Transfer the temporary text file to the database server machine or to a cloud storage supported by the target database.
  3. Instruct the target database to upload the temporary file in a bulk load mode using a special custom SQL command.

Exporting data into a text file was described previously. To bulk load data into a database, send a special custom SQL statement to the database using "Database command" action, command "Custom command".

Bulk loading typically has very good performance, and allows fast loading big tables due to database-specific optimizations, although, it requires learning how to compose the necessary SQL command. See the links below to find documentation on bulk load commands in some popular RDBMSes:

Hint: Insert parameters into SQL commands in curly braces. When sending a command, a parameter name in curly braces will be replaced with the parameter's value.

Hint: You can also use "Database command" to trigger stored procedures.

Note that export actions and the "Database command" action are not calculated automatically, even when Auto-run is on. They are considered side-effect actions because they affect external data and systems. Such actions should be run manually by pressing "Run project" on the main toolbar, or F5.

Updating rows in a database table

The "Update database table" action can be used to update values in a database table. Under the hood, the action executes an SQL UPDATE statement. Read more about the action.

Deleting database rows using a query

One way to delete database rows is to use a query in the "Delete database rows" action. The action deletes rows using the filtering conditions of the query. Column selections in the query are ignored. Under the hood, the action generates a SQL request DELETE FROM [table] WHERE [condition].

The "Delete database rows" action has only one setting — a query.

Hint: Use the query preview to see the rows that will be deleted by the action.

Deleting matching database rows

Another way to delete database rows is using the "Delete matching database rows" action. This action deletes rows where key fields in the database table match key fields in the current EasyMorph table. Under the hood, the action creates a temporary table in the database with keys from the EasyMorph table, then generates a SQL request DELETE FROM [table] WHERE [key] EXISTS IN [temp.table], or similar.

Note that matching works only for key fields of particular types:

  • Integer numbers
  • Text strings
  • Dates without time

If at least one value in matched columns has another type, the action will fail.

This action is frequently used together with "Select matching database rows". It's also convenient in the cases where a list of IDs to delete is obtained from a 3rd party data source (e.g. a spreadsheet).