transformations:exportdb
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
transformations:exportdb [2020/01/22 12:59] – dmitry | transformations:exportdb [2021/02/12 07:02] – 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 in bigger batches can be slightly faster on narrow tables (i.e. tables with few fields). For wide tables (i.e. table with hundreds of fields) or when columns contain long strings | + | Note that the operation effectively // |
- | Export will convert cell values only in the following cases: | + | ===Performance=== |
+ | Under the hood, the exporting is performed using SQL INSERT statements by batches of 10, 100, 1'000, or 10'000 rows. Exporting | ||
- | * Boolean cell to a numeric column - 1 or 0 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: |
- | * 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 by the target database | + | |
- | * Any cell to a text column or a column of unknown type - will be exported as text | + | |
- | * 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. | + | ===Data type conversion=== |
+ | Exporting will convert cell values only in the following | ||
- | + | * Boolean cell to a numeric column exported as 1 (TRUE) or 0 (FALSE) | |
- | The operation is //atomic// meaning that it either successfully exports all rows, or nothing (in case of failure). | + | * Numeric cell to a date or a date/time column - the cell value is converted to a date or a date-time literal according to the SQL dialect |
+ | * 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 | ||
- | Note that the operation effectively //appends// rows to existing ones. If you need to remove old data use the [[sqlcommand|Database command]] | + | 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 |
- | Some databases have performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using bulk load statements in the [[sqlcommand|Database command]] action when exporting more than 1 million rows at once. | + | ===Error capture=== |
- | ==See also== | + | 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:// | ||
- | * [[transformations: | + | |
+ | | ||
+ | * [[transformations: |
transformations/exportdb.txt · Last modified: 2023/10/14 16:20 by craigt