transformations:exportdb
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
transformations:exportdb [2020/01/22 12:59] – dmitry | transformations:exportdb [2021/02/12 06:56] – dmitry | ||
---|---|---|---|
Line 3: | Line 3: | ||
Exports the current dataset into the database table which name is specified either explicitly or using a [[: | Exports the current dataset into the database table which name is specified either explicitly or using a [[: | ||
- | Under the hood, the exporting is performed using SQL INSERT statements by batches of 10 or 100 or 1000 rows. Exporting | + | The operation is //atomic// meaning that it either successfully exports all rows, or nothing (in case of failure). |
- | Export will convert cell values only in the following cases: | + | Note that the operation effectively //appends// rows to existing ones. If you need to remove old data use the [[sqlcommand|Database command]] action. |
- | * Boolean cell to a numeric column - 1 or 0 will be exported | + | ===Performance=== |
- | * Numeric cell to a date or a datetime column - source number will be treated as an OADate and converted to a date or a datetime literal according to the format, supported | + | Under the hood, the exporting is performed using SQL INSERT statements |
- | * Any cell to a text column | + | |
- | * Error cell will be always exported as null | + | |
- | In all the other cases when cell type doesn’t match column type a NULL value will be exported. | + | 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=== | |
- | The operation is //atomic// meaning that it either successfully exports all rows, or nothing (in case of failure). | + | Exporting will convert cell values only in the following cases: |
- | Note that the operation effectively | + | * Boolean cell to a numeric column exported as 1 (TRUE) or 0 (FALSE) |
+ | * Numeric cell to a date or a date/time column - the cell value is converted | ||
+ | * Any cell to a text column or a column of unknown type is exported as a text literal | ||
+ | * Error cells are always exported as NULL | ||
+ | |||
+ | 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 | ||
+ | |||
+ | ===Error capture=== | ||
+ | |||
+ | The action has an option "When exporting a batch fails" that specifies behavior in case a batch of rows fails to export. | ||
- | Some databases have performance bottleneck for batches | + | ^ Error capture mode ^ Behavior |
+ | | Halt execution, roll back already exported | ||
+ | | Add new column to flag rows in batches that fail | ||
- | ==See also== | + | ===See also=== |
* [[http:// | * [[http:// | ||
+ | * [[transformations: | ||
* [[transformations: | * [[transformations: |
transformations/exportdb.txt · Last modified: 2023/10/14 16:20 by craigt