transformations:exportdb
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
transformations:exportdb [2021/02/12 07:02] – dmitry | transformations:exportdb [2023/10/14 16:20] (current) – update to latest wiki style craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Export into database table ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Export / Database\\ | ||
- | Exports | + | \\ |
+ | =====Description===== | ||
+ | This action exports | ||
- | Note that the operation effectively //appends// rows to the target table. If you need to remove old data use the [[sqlcommand|Database command]] action. If you need to update rows, use the [[transformations: | + | Note that the operation effectively //appends// rows to the target table. If you need to remove old data use the [[transformations: |
+ | \\ | ||
+ | =====Action settings===== | ||
+ | ^Setting | ||
+ | |Connector< | ||
+ | |||
+ | < | ||
+ | |||
+ | \\ | ||
+ | ====Export settings==== | ||
+ | ^Setting | ||
+ | |Table name< | ||
+ | |Column mapping |Select the method for mapping the dataset columns to the fields in the database table.\\ | ||
+ | |||
+ | < | ||
+ | |||
+ | \\ | ||
+ | ====Options settings==== | ||
+ | ^Setting | ||
+ | |When value doesn' | ||
+ | |When exporting a batch fails|Choose how EasyMorph will handle situations when the export fails.\\ | ||
+ | |||
+ | \\ | ||
+ | ====Custom SQL settings==== | ||
+ | ^Setting | ||
+ | |Execute custom SQL before exporting|To run an ad hoc SQL process on the data prior to exporting, check this setting //on// and enter an SQL statement. | ||
+ | |||
+ | |||
+ | \\ | ||
+ | =====Remarks===== | ||
===Performance=== | ===Performance=== | ||
Under the hood, the exporting is performed using SQL INSERT statements by batches of 10, 100, 1'000, or 10'000 rows. Exporting in bigger batches can be faster and is recommended for narrow tables (i.e. tables with few fields). For wide tables (i.e. tables with hundreds of fields) or when columns contain long strings use smaller batches, otherwise an export statement may fail due to the SQL statement limit of the database driver. | Under the hood, the exporting is performed using SQL INSERT statements by batches of 10, 100, 1'000, or 10'000 rows. Exporting in bigger batches can be faster and is recommended for narrow tables (i.e. tables with few fields). For wide tables (i.e. tables with hundreds of fields) or when columns contain long strings use smaller batches, otherwise an export statement may fail due to the SQL statement limit of the database driver. | ||
- | Some databases have a performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using the [[transformations: | + | Some databases have a performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using the [[transformations: |
+ | \\ | ||
===Data type conversion=== | ===Data type conversion=== | ||
Exporting will convert cell values only in the following cases: | Exporting will convert cell values only in the following cases: | ||
Line 20: | Line 54: | ||
In all the other cases when the cell type doesn’t match the target column type either a NULL value is exported, or the exporting fails, depending on action settings. | In all the other cases when the cell type doesn’t match the target column type either a NULL value is exported, or the exporting fails, depending on action settings. | ||
- | ===Error capture=== | + | \\ |
- | + | =====Community examples===== | |
- | The action has an option "When exporting a batch fails" that specifies behavior in case a batch of rows fails to export. | + | |
- | + | ||
- | ^ Error capture mode ^ Behavior | + | |
- | | Halt execution, roll back already exported batches | + | |
- | | Add new column to flag rows in batches that fail | A new column labelled " | + | |
- | + | ||
- | ===See also=== | + | |
* [[http:// | * [[http:// | ||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
* [[transformations: | * [[transformations: | ||
* [[transformations: | * [[transformations: | ||
* [[transformations: | * [[transformations: | ||
+ |
transformations/exportdb.txt · Last modified: 2023/10/14 16:20 by craigt